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