Skip to Content
0

Display of cumulated figures in Design Studio

May 03, 2017 at 08:24 AM

267

avatar image

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)
10 |10000 characters needed characters left characters exceeded

Tobias - not sure I have an answer, but could you share screen shots of your initial view in Design Studio, and perhaps of your BEx query as well?

0

Hi Tammy,

I had to manipulate data a bit due to confidence reasons, but the Design Studio intial view looks similar to this:

BEx query looks like this:

Thanks for any idea.

ds-initial-view.png (105.9 kB)
bex-view.png (97.1 kB)
0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mustafa Bensan May 04, 2017 at 09:08 AM
2

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.


Show 9 Share
10 |10000 characters needed characters left characters exceeded

Dear Mustafa,

thanks for your answer.

I set everything up as described in your answer, but now my questions is if you have marked the "Cumulated" field in the "Calculations" tab of the columns?

I didn't have it marked now and it unfortunately did not work, as I still only see month figures. I tried to add an "Accumulative Sum" dynamic calculation column in DS initial view, but there were also values shown for months 6-12 of current year.

Thanks for your support!

Kind regards

Tobias

0

Hi Tobias,

Is your "CY" column marked as Cumulated in your BEx Query?

Regards,

Mustafa.

0

Hi Mustafa,

no, not until now.

Regards, Tobias

0

In that case, can you set the "CY" column to Cumulated and leave the Current Year calculated column as not cumulated? Let me know if that resolves the issue.

0

Dear Mustafa,

unfortunately it is not working.

I tried two different versions:

1. PY + CY = Cumulated, Current year (cumulative) = not cumulated

Outcome was similar to this:

2. PY + CY + Current year (cumulative) = Cumulated

Outcome was similar to this:

Do you have any other idea?

Thanks and kind regards

Tobias

v1.png (9.3 kB)
v2.png (9.1 kB)
0

Hi Tobias,

I've tried to recreate a solution I implemented in the past. Let me re-test and get back to you then.

Regards,

Mustafa.

0

Hi Tobias,

Okay, I think I have a solution for you. Please try the following steps:

1. For all three columns, PY, CY and Current Year (cumulative) SWITCH OFF the "cumulated" indicator in the Display properties

2. For characteristic Posting Period, SWITCH ON the "Cumulated Value Display" property as shown below:

Also, in your example, can you confirm that the current period is period 5? And can you also provide screenshots of the restrictions for PY and CY as well as the formula in Current Year (Cumulative)? It seems unusual that just the first period value of 10,000 is repeated in your first example output.

Anyway, I hope the above steps now solve your issue. Let me know.

Regards,

Mustafa.

0
Show more comments

That's excellent, Tobias. Thanks for sharing the results. I'm glad it finally worked out. Have a good weekend :)

0
Tammy Powlas
May 03, 2017 at 12:42 PM
0

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?

Show 1 Share
10 |10000 characters needed characters left 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

0
Koen Hesters May 03, 2017 at 01:00 PM
0

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)


Share
10 |10000 characters needed characters left characters exceeded