Date vs Previous Month of Same Date using Set expression

[azucna122]

Hi, I'm new to Qlik,

I have in my script the following simple code:

TmpData:
LOAD
Date(LOAD_DATE, 'DD/MM/YYYYYY') as LOAD_DATE,
SALES
FROM NewFolder\Tests.xlsx;

I have one filter per month and two kpi, in which one I want to show the current month's sales count and the other the previous month's count.

but when I filter the current month (Oct), the value of the current kpi is correct (22), but the value of the previous month is not correct, should be (30) and not (0)...

the expressions I use are:
declared variables:
= Max (LOAD_DATE) --> Var_yearmonth_filt.
= $(Var_yearmonth_filt) - 1 --> Var_month_prev
Current_Month:
COUNT({<[LOAD_DATE] ={$(Var_yearmonth_filt)} >} SALES) --> this code works
Previous Month:
COUNT({<[LOAD_DATE]={$(Var_month_prev)} >} SALES) --> this code doesn't work, I also tried this set analysis

COUNT({<LOAD_DATE={'>=$(=MonthStart( today() ,-1) )<=$(=MonthEnd( today(),-1 ) )'} >} SALES)  but it also doesn't work

[HeshamKhja1]

Your first expression using variable Var_month_prev seems to be incorrect as you are calculating yesterday not last month.

But the second expression seems to be correct. I want to ask what field are you using for Month filtration? Can you try to add it to the expression like this: 

COUNT({<Month=, LOAD_DATE={'>=$(=MonthStart( today() ,-1) )<=$(=MonthEnd( today(),-1 ) )'} >} SALES)

And I have a question for you, Date(LOAD_DATE, 'DD/MM/YYYYYY') why in the format you have six 'YYYYYY'?

[vincent_ardiet_]

Try to replace Var_yearmonth_filt with =Max ({1}LOAD_DATE)
This makes your Max insensitive to any selection. Without {1} if you select a month in the past, the max is recomputed and giving you the selected value.

[azucna122]

hi HeshamKhja1,

this expression does not work ...
the format I give it, because the field comes with time (DataTime), and I only want the date.
the date filter is: Month([LOAD_DATE])

[azucna122]

hi vincent_ardiet,

it also doesn't work ...

[vincent_ardiet_ · Accepted solution]

 My bad, I have misunderstood the issue.
Try this for the previous month expression:
COUNT({<[YEAR],[MONTH],[DAY],[LOAD_DATE]={$(Var_month_prev)} >} SALES)