topic Re: Expression issue for previous month in QlikView

Expression issue for previous month

spikenaylor1 — Wed, 25 Nov 2020 16:16:04 GMT

My source date field is

[Date Closed]

I have a master calendar

I define MonthName([Date Closed]) as Month

all works

I select Feb 2019 on my chart, list, etc

I would like to display the value from the previous month

I have tried various options.

I have a variable

vPrevMonth = Month-1

vPrevMonth displays Jan 2019 so is working correct

my expression is

=Count({<Month={'$(vPrevMonth)'},RFT = {'YES'}>} RFT)

=Count({<Month={$(vPrevMonth)},RFT = {'YES'}>} RFT)

I just cant get it to work

anyone with any pointers

regards

Re: Expression issue for previous month

vishsaggi — Wed, 20 Feb 2019 16:45:23 GMT

Can you create something like this in your data model mastercalendar like

LOAD *,
MonthName([Date Closed]) as Month,
Floor(MonthName([Date Closed])) as Month#
From Mastercalendar;

Then create variable
LET vPrevMonth = [Month#]-1;

Then use your expr like

=Count({<[Month#]={$(vPrevMonth)},RFT = {'YES'}>} RFT)
OR
=Count({<[Month#]={'$(vPrevMonth)'},RFT = {'YES'}>} RFT)

Re: Expression issue for previous month

sunny_talwar — Wed, 20 Feb 2019 19:14:54 GMT

Try this

=Count({<Month = {"$(=MonthName(AddMonths(Max(Month), -1)))"}, RFT = {'YES'}>} RFT)

Re: Expression issue for previous month

andrescc — Thu, 21 Feb 2019 05:20:28 GMT

Hi,

If you use the field Month as dimension in your chart, you can't show the previous month with set analysis. You have to create the field inside the script

If you want create a chart with other dimensions (not like Month or any field of time) you can try this:
In your Master calendar define
((Year([Date Closed])-1)*12 + Num(Month([Date Closed]))) as Month_ID

and set analysis
=Count({$< Month_ID = {$(=Max(Month_ID-1))},RFT = {'YES'}>} RFT)

I hope it will work

Regards,
ACC