Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Full Accumulation Doesn't Work in Formula

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!

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thanks a lot Jonathan, works like a charm!