topic Re: Previous Month in Set Analys in QlikView

Previous Month in Set Analys

lucasx15 — Thu, 28 Apr 2022 14:18:39 GMT

Hello everybody!

I read some forum posts about "previous month". However, I was not successful in applying.
I have the following situation, I need to lock my Set Analys so that it always takes the previous month. My expression is like this:

COUNT({1<CD_SETOR={22}, MESANO_PRIMDIA={"$(=Monthstart(Today())-1)"}>} DISTINCT NR_NUMERIC))

Re: Previous Month in Set Analys

vchuprina — Thu, 28 Apr 2022 14:27:43 GMT

Hi,

You can add '-1' as a second parameter to monthsstart function

COUNT({1<CD_SETOR={22},
MESANO_PRIMDIA={"$(=Monthstart(Today(),-1))"}>}
DISTINCT NR_NUMERIC))

https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/monthstart.htm

Regards,

Vitalii

Re: Previous Month in Set Analys

lucasx15 — Thu, 28 Apr 2022 14:53:00 GMT

Thank you for reply, Vitalii.

However, even using the second argument the field still appears null for me in the display. The dimension that I am using MESANO_PRIMDIA. Do I need to put the -1" argument in the dimension as well?

Re: Previous Month in Set Analys

vchuprina — Thu, 28 Apr 2022 14:58:14 GMT

What do you have in MESANO_PRIMDIA column?

Re: Previous Month in Set Analys

lucasx15 — Thu, 28 Apr 2022 17:05:32 GMT

In column, MESANO_PRIMDIA ,has the date in the format: "MMM/YYYY"

Re: Previous Month in Set Analys

vchuprina — Thu, 28 Apr 2022 20:33:44 GMT

In this case you should transform output of MonthStart(Today(),-1) expression to MMM/YYYY format, your expressions are:

COUNT({1<CD_SETOR={22},

MESANO_PRIMDIA={"$(=Date(MonthStart(Today(),-1), 'MMM/YYYY'))"}>}

DISTINCT Value)

Based on today() function, today is 4/28/2022, Qlik will show the count for the previous month - Mar/2022 even if you select Feb/2022 in MESANO_PRIMDIA

COUNT({1<CD_SETOR={22},

MESANO_PRIMDIA={"$(=Date(MonthStart(Max(MESANO_PRIMDIA),-1), 'MMM/YYYY'))"}>}

DISTINCT Value)

Based on Max(MESANO_PRIMDIA), if the latest MESANO_PRIMDIA in your application is Apr/2022 Qlik will show the count for Mar/2022. If you select Mar/2022 Qlik will show data for Feb/2022

Regards,

Vitalii