Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

muralipksi
New Contributor

pulling previous years data while current year selected

Hi,

I have data in below format in a table.

YearMonthMeasure
201501385
201502452
201503652
201601584
201602548
201603500
201701545
201702485
201703458

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.

MonthCurrentyearvalueprevious year valuDifferencevalue
Jan545584-39
Feb485548-63

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

Thanks

1 Solution

Accepted Solutions
justinphamvn
Contributor II

Re: pulling previous years data while current year selected

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

SetAnalysis Year.png

Hopes this help

Justin.

6 Replies
justinphamvn
Contributor II

Re: pulling previous years data while current year selected

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

SetAnalysis Year.png

Hopes this help

Justin.

muralipksi
New Contributor

Re: pulling previous years data while current year selected

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
New Contributor III

Re: pulling previous years data while current year selected

Good one Justin. Helps to handle historical data issues.

Highlighted
girishsrini
New Contributor III

Re: pulling previous years data while current year selected

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
Contributor II

Re: pulling previous years data while current year selected

Hi Girish,

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

Thank you

Justin

girishsrini
New Contributor III

Re: pulling previous years data while current year selected

Agreed Justin.

Have already posted separately. Thanks.

Community Browser