Skip to Content

Display of cumulated figures in Design Studio

Dear all,

I want to have a chart in Design Studio which looks like this:

Grey bars are cumulated month figures of previous year, pink bars are cumulated month figures of current year. If there is no value in a current year's month, I want to show no bar for the respective month.

What I get is the following, and I am trying around since a while, but I do not find a feasible solution:

Whenever I try to show cumulated figures in BEx or in Initial query view in Design Studio, I get the second result. I have the graphomate Charts add-on in use.

Do you have any idea how this could be solved? I don't mind if I could solve it in BEx or if I could solve it in Design Studio.

Thanks in advance!

correct.png (2.1 kB)
wrong.png (2.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 04, 2017 at 09:08 AM

    Hi Tobias,

    I have faced the same issue in the past. To expand on Tobias' suggestion, here is the solution I applied that is dynamic, without the need for hard-coding values. It is based on using a combination of BEx Variables and Formula Variables.

    1. Structure your BEx Query as follows:

    In the Columns structure include 3 items:

    (i) Prior Year - a selection

    (ii) Current Year - a selection

    (iii) Current Year (Cumulative) - a formula

    2. In the Rows section include your Month dimension

    Your overall query structure should look something like this:

    3. The Prior Year column should be restricted using the appropriate BEx Exit Variable for Current Year as follows:

    4. The Current Year column should be restricted using the appropriate BEx Exit Variables for Current Year and Current Month range as follows:

    5. The Current Year Formula column is defined as follows:

    The boolean formula above results in the Current Year value when the month is less than or equal to the Current Month and a zero value otherwise.

    In the above example, ZVAR_MONTH_SELECTION is a Formula Variable which represents the Current Month. Ideally it should be one of the standard SAP BEx Exit Variables which automatically calculates the current month but you could also use an Input BEx Variable.

    ZVAR_MONTH_CHART is a Formula Variable which references the required Month dimension in the query. My example references the standard BW Calendar Month (0CALMONTH2), as follows:

    6. Your chart should be assigned the columns "Prior Year" and "Current Year (Cumulative)"

    Let me know if you have any questions.

    Regards,

    Mustafa.

    Add comment
    10|10000 characters needed characters exceeded

  • May 03, 2017 at 12:42 PM

    Tobias - Thank you for the screen shots; this helps a lot.

    This is strange; it should not show that second bar. Have you contacted Graphomate support?

    Add comment
    10|10000 characters needed characters exceeded

    • Tammy,

      if you set your Key Figure in BEX as cumul in tab calculation, the Key Figure will show for the months without data the cumul for the month, so it's maybe not that odd that in design studio you'll see the graph displaying those numbers

      Koen

  • May 03, 2017 at 01:00 PM

    Hi Tobias,

    I tried on year basis to get rid of the cumul when there is no update, so in 2014 I have numbers and in 2015 not.

    I created in BEx a formula with a Boolean condition so when I show data of 2014 it get multiplied by 1 and data for 2015 get multiplied by 0

    results in:

    <Formule 1> is with condition, <Aantal Combipremies> is without (the cumul that goes on and on, even when there is no data)

    Add comment
    10|10000 characters needed characters exceeded