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
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?
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
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
