Skip to Content
avatar image
Former Member

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

Apr 28, 2017 at 08:13 AM | 135 Views

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)
SAP BusinessObjects Design Studio
design studio | karol kalisz
Add comment
10|10000 characters needed characters exceeded

  • Tammy Powlas
    Apr 28, 2017 at 09:07 AM

    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?

  • Get RSS Feed

2 Answers

  • avatar image
    Lakshmikanth Adharapurapu
    Apr 28, 2017 at 09:19 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Apr 28, 2017 at 11:42 AM 

      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

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

    Hi All,

    Any update?

    Regards,
    Piyush

    Add comment
    10|10000 characters needed characters exceeded