Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

siepe1990
New Contributor III

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
MVP
MVP

Re: Full Accumulation Doesn't Work in Formula

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
2 Replies
MVP
MVP

Re: Full Accumulation Doesn't Work in Formula

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
Highlighted
siepe1990
New Contributor III

Re: Full Accumulation Doesn't Work in Formula

Thanks a lot Jonathan, works like a charm!