Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
Good one Justin. Helps to handle historical data issues.
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
Hi Girish,
Please create a new thread and describe your problems, because this thread had correct answer
Thank you
Justin
Agreed Justin.
Have already posted separately. Thanks.