Skip to Content
0

How to show a Straight line (parallel to X-axis) in a Chart.

Apr 28, 2017 at 08:13 AM

127

avatar image
Former Member

Hi All,

I am working on a requirement where I need your assistance.

My requirement is to show 1 dimension and 4 measures in a chart.

Dimension: Months (of current quarter)

Measures:

  • 1.Average of Revenue based on New Contracts in the past Quarter....(Goal)
  • 2.Average of Revenue based on Expiring Contracts in the current quarter...(Goal)
  • 3.Revenue based on Contracts with status “New“ in the current quarter.
  • 4.Revenue based on Contracts with status “Renewed” in the current quarter.

In the graph, Measure 3 and Measure 4 will show value for the months of current quarter whereas Measure 1 and Measure 2 are static fields (Threshold/Goal) and will remain constant/fixed value throughout a quarter (irrespective of months). So have to show straight lines for these two.(in the graph shown in Red and Black parallel to X-axis).

I have created the required measures in the universe.The problem occurs when I try to bring all of these measures in 1 query. Measure 1 and Measure 2 gets bifurcated according to month(cause of group by which is obvious). But I want them to display same value for each month so as to get a straight line. Also, Measure 1 is showing value for the previous quarter so when I run the query I see previous quarter’s month as well.

I am using 1.6 SP2 Patch1.
Universe is based on Hana JDBC

Any inputs will be appreciated.

Regards,
Piyush

req.png (6.6 kB)
10 |10000 characters needed characters left characters exceeded

Hello Piyush, on the surface, it sounds like it could be dependent on the modeling back in the Universe; perhaps if you share screen shots of the model in the universe?

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Lakshmikanth Adharapurapu Apr 28, 2017 at 09:19 AM
0

Hi Piyush,

I recommend you to create a measure which takes the over all result value for Measure 1 for the past quarter and similarly create measure 2 based on overall result for expiring conracts, Then build a query

Your measure 1 and Measure 2 always displays overall result value for the respective quarters and never segragate based on months, Hope by this way you can achieve your requirement?.

Feel free to let me know incase of issues.

BR,

Lakshmikanth Adharapurapu

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Tammy/Lakshmikanth,

Thank you for your inputs.
I have created the measures in the universe but as soon as I include months in that query it gets bifurcated.

Here are the objects I created:

==========================For finding 1st and last day of current quarter=========================

Q-Start:

Add_days( Add_months( current_date,(mod( month(current_date) -1, 3 )) * -1) ,dayofmonth(current_date)* -1 + 1 )

Q-End:

Case 
When mod(month(current_date), 3) = 2 Then last_day(add_months(current_date, 1))
When mod(month(current_date), 3) = 1 Then last_day(add_months(current_date, 2))

When mod(month(current_date), 3) = 0 Then last_day(current_date )
END

===========================For finding 1st and last day of last quarter========================

LQ-Start:

Add_months(Add_days( Add_months( current_date,(mod( month(current_date) -1, 3 )) * -1) ,dayofmonth(current_date)* -1 + 1 ),-3)

LQ-End:

Add_days( Add_months( current_date,(mod( month(current_date) -1, 3 )) * -1) ,dayofmonth(current_date)* -1)

=======================================================================================

Measure 1 SQL

Sum(CASE WHEN "_SYS_BIC"."Sales_Dashboard_V002/SALES_DASHBOARD_14K"."SHIP_COND" = '01' and @Select(CRM14K\End Date) between @Select(CRM14K\LQ-Start) and @Select(CRM14K\LQ-End) THEN "_SYS_BIC"."Sales_Dashboard_V002/SALES_DASHBOARD_14K"."AMOUNT" ELSE 0 END)/3

Measure 2 SQL

Sum(Case When (@Select(CRM14K\End Date) between @Select(CRM14K\Q-Start) and @Select(CRM14K\Q-End)) and @Select(CRM14K\End Date)>current_date Then "_SYS_BIC"."Sales_Dashboard_V002/SALES_DASHBOARD_14K"."AMOUNT" ELSE 0 END )/3 

And have created Measure 3 and Measure 4 as well and when I create query including all of these measures along with Month, I get the issue described above.

Please suggest.

Regards,
Piyush

0
avatar image
Former Member Apr 29, 2017 at 09:45 PM
0

Hi All,

Any update?

Regards,
Piyush

Share
10 |10000 characters needed characters left characters exceeded