In a set analysis, getting a month and the month before it.

[M_Bilto]

Hello qlik community

I have a problem that im suffering with and a senior of mine before me that struggled with it too. Currently on the April version im trying to do this in a set analysis

Keep in mind that i have constructed a date to look like this: if this was a date "3/25/2018 10:10:10 AM" it turns it to "3/2018" so only the month and the year

KPI 1 (On this KPI i want to show the latest year and month in the data)

so im writing something like this

Count( {$<Month_Year = {'$(=Max(Month_year))'}>} ID) ----> this works

KPI 2 (On this KPI i want to show the year and month prior to the selection in KPI 1)

and for this one i try this

Count( {$<Month_Year = {'$(=Max(Month_year) -1)'}>} ID) -----> this doesnt work

Count( {$<Month_Year = {'$(=AddMonths(Max(Month_year), -1) )'}>} ID)  -----> this doesnt work either

also any kind of month manipulation just doesn't work in set analysis. Note that these operations work in a Text/Image Chart and i verified that it can work. but in set analysis it doesn't.

please help.

Thank you

[sunny_talwar · Accepted solution]

If you create Month_Year like this in the script

Date(MonthStart(date), 'M/YYYY') as Month_Year

then try this

Count( {$<Month_Year = {"$(=Date(AddMonths(Max(Month_year), -1), 'M/YYYY'))"}>} ID)

Basically, you need to make sure that format within your set analysis matches with the format of your Month_Year field.

[M_Bilto]

Thank you so very much!