topic Re: How to calculate change from prior year in table in App Development

How to calculate change from prior year in table

daveatkins — Tue, 10 Jan 2017 19:56:39 GMT

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 — Tue, 10 Jan 2017 20:06:43 GMT

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

Re: How to calculate change from prior year in table

daveatkins — Tue, 10 Jan 2017 20:31:47 GMT

thank you; helpful to rule this out. I will go back to "above" function. Problem I have is that the data is like

year, other field, value

where other field is 2nd dimension. So "above" needs to point to several rows above the current row. I've tried using the offset, but it did not work

Re: How to calculate change from prior year in table

daveatkins — Tue, 10 Jan 2017 21:22:25 GMT

frustratingly, the above function will not do what I need. The data is sorted by year, otherfield. Above would work if the sort order were reversed, but that defeats the point of the presentation.

Re: How to calculate change from prior year in table

Oleg_Troyansky — Tue, 10 Jan 2017 21:54:33 GMT

I think you can combine Above() with the AGGR() function, to overcome the challenge of incorrect sorting. Something like this:

sum(

AGGR(

above(Sum(Sales)),

OtherField, Year)

)

This AGGR function will sort the data by OtherFIeld first, and Year next. Make sure that the years are sorted properly in the data. If they are not, you can sort them within AGGR() :

sum(

AGGR(

above(Sum(Sales)),

OtherField,

(Year, NUMERIC, ASCENDING)

)

I'm quoting from memory, so double check the syntax.

AGGR() is a powerful function, and it's relatively little-understood among developers. I'm teaching it with a lot of details at the Masters Summit for Qlik - see if our agenda is right for you.

cheers,

Oleg Troyansky

Re: How to calculate change from prior year in table

daveatkins — Tue, 10 Jan 2017 22:52:09 GMT

this appears to work!