pulling previous years data while current year selected

[Anonymous]

Hi,

I have data in below format in a table.

YearMonth	Measure
201501	385
201502	452
201503	652
201601	584
201602	548
201603	500
201701	545
201702	485
201703	458

I'm looking to show difference of the values by year in tabular format as shown in the below table, when the user selects year from filter then the table in Qlik Sense should show difference between selected year and previous year as shown in below table.

Month	Currentyearvalue	previous year valu	Differencevalue
Jan	545	584	-39
Feb	485	548	-63

I tried different ways including set analysis, but the previous year values are not getting picked. Appreciate any help.

Thanks

[justinphamvn · Accepted solution]

Hi murali y,

Try like this:

Script Load:

TABLE_1:
LOAD * INLINE [
    YearMonth, Year, Month, Measure  
    201501, 2015, Jan, 385
    201502, 2015, Feb, 452
    201503, 2015, Mar, 652
    201601, 2016, Jan, 584
    201602, 2016, Feb, 548
201603, 2016, Mar, 500
    201701, 2017, Jan, 545
    201702, 2017, Feb, 485
201703, 2017, Mar, 458
];

Chart Function:

Measure Current Year 

Sum({<Year= {">=$(=Max(Year)) <=$(=Max(Year))"} >}Measure)

Measure Prev Year

Sum({<Year= {">=$(=Max(Year) - 1) <=$(=Max(Year) - 1)"} >}Measure)

This is look like the picture below

Hopes this help

Justin.

[Anonymous]

Brilliant, It worked for me, Thanks for the help Justin. I was unable to figure out what was wrong with my script, thanks for the help once again.

Murali

[girishsrini]

Good one Justin. Helps to handle historical data issues.

[girishsrini]

Hi Justin,

Could you look at the below post, Please check if you can find any solution?

Above() in QlikSense is not working for the same criteria beyond once

Regards,

Girish

[justinphamvn]

Hi Girish,

Please create a new thread and describe your problems, because this thread had correct answer

Thank you

Justin

[girishsrini]

Agreed Justin.

Have already posted separately. Thanks.