5 Replies Latest reply: Jan 10, 2017 5:52 PM by David Atkins RSS

    How to calculate change from prior year in table

    David Atkins

      I just cannot figure out the syntax and hope someone can help.

       

      I want to display a table like this (simple version):

       

      year, value, change from last year

       

      2012, 10, -

      2013, 11, 10%

      2014, 12.1, 10%

      2015, 24.2, 100%

       

      I need an expression for the 3rd column.

       

      The actual data is more complicated (it is year to date and there are other dimensions), but the expression should be something like this. (Current Year - Last Year)/Last Year

       

      count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year=,Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)

      -

      count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year={'(Year-1)'},Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)

      )

      /

      count(DISTINCT {<CountFlag={'1'},MonthNum={"<=$(=$(vCYTDMon))"},Month=,Year={'(Year-1)'},Quarter=,ReportingDate=,WeekStart=,Mode={'yyz'}>} Account)

       

      How do I get that Year-1 concept into the set?

       

      I have also tried the Above() function, but I need this more precise approach to work.

        • Re: How to calculate change from prior year in table
          Oleg Troyansky

          Hi David,

           

          A few comments:

           

          - If you can use the Above() function for your calculation, it would be the easiest solution.

           

          - You can't use Set Analysis in this chart (with the field Year being a Dimension) because Set Analysis conditions are only validated once, before the chart is even calculated, therefore they can't be sensitive to the values of your Dimensions (such as Year).

           

          - In order to overcome the previous issue, developers often create a so called "As of Date" table - where the single Date field is being split into two fields - "Display Date" and "Transaction Date". The "As of Date" table contains both dates and a set of flags that determine the relationship between the two - for example, CYTD_Flag, PYTD_Flag, etc...

           

          You can find several free white papers describing the concept of the "As of Date" table, including my blog article here:

          QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

           

          You can also read a more detailed explanation with several hands-on exercises in my book QlikView Your Business.

           

          Finally, you can learn this technique, along with many other advanced Qlik techniques, at the Masters Summit for Qlik.

           

          Cheers,

          Oleg Troyansky