2 Replies Latest reply: Mar 26, 2015 10:46 AM by Stefan van Diepen RSS

    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!