Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear fellow Qlikviewers,
I have a question regarding the Full Accumulation Option we can use whilst building a Chart.
Let me explain what I'm trying to achieve.
As a dimension I use the Business Day Nr. we have for the corresponding day in the month (from 1-22 in March).
I have 3 columns I want to end up with:
- Actual
- Budget
- Variance % vs. Budget Month-to-Date (MtD)
The first twp are easy to achive, but to be complete, I'll list their formula's here as well:
- Sum ({<Scenario = {'ACT'}, Month = {$(vCurrMonth)}>} SHP)
- Sum ({<Scenario = {'BGT'}, Month = {$(vCurrMonth)}>} SHP)
Now comes the tricky part. I want to include a Variance % vs Budget on a MtD-level.
For that, I have created three extra Expressions:
- Actual MtD
- Budget MtD
- Variance % vs BGT
Both the Actual MtD and the Budget MtD have the same formula as used above, only this time with a 'Full Accummulation' ticked.
The Variance % vs BGT then is calculated using the Expressions-names of the two MtD-expressions:
([ACT MtD] - [BGT MtD])/[BGT MtD]
However, it does not give me the outcome I hope for. See below for the outcome from Qlikview:
The MtD-figures are looking fine, doing exactly what should be done.
However, when looking at the %, you can see that Qlikview simply did (ACT - BGT) / BGT instead of taking the MtD-figures.
Percentages MtD should be 25,7% - 13,6% - 11,6% - 8,8% - 7,7% for the first 5 days.
Can anyone explain to me why Qlikview does this and if there is a workaround?
Thanks in advance for any help!
Hi
Full Accumulation does not change the underlying values, so you get the result you observed. To solve this, you will need to perform the accumulation in your expression, rather than relying on the chart. The accumulation expression would look like this:
=RangeSum(Above(<your expression>, 0, RowNo()))
(<your expression is the current expression for ACT, BGT etc)
HTH
Jonathan
Hi
Full Accumulation does not change the underlying values, so you get the result you observed. To solve this, you will need to perform the accumulation in your expression, rather than relying on the chart. The accumulation expression would look like this:
=RangeSum(Above(<your expression>, 0, RowNo()))
(<your expression is the current expression for ACT, BGT etc)
HTH
Jonathan
Thanks a lot Jonathan, works like a charm!