Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
In an expression I'm writing I want to consider only those records corresponding to last month. I have a calendar with the variable link_YearMonth that takes values such as 2018-01, 2017-12, etc.
I wanted to use Set Analysis in the following manner:
link_YearMonth=Date(AddMonths(Today(),-1),'YYYY-MM')
The complete expression looks like this:
Sum({<[link.id]={'255500','255480','255460'}>}
Aggr(Avg({<[link.id]={'255500','255480','255460'},link_YearMonth=Date(AddMonths(Today(),-1),'YYYY-MM'),link_WeekDay-={'Sat','Sun'},PeakPeriodId={'1'}>}traveltime/60),[link.id]))
Whenever I add that YearMonth condition in bold, the expression returns zero. Do you see any problem in using it? Is there any alternative condition I can use to restrict the values to last month?
Thanks
try it like this
link_YearMonth={"$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}
try it like this
link_YearMonth={"$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}
I would suggest to change AddMonths() to MonthStart here
link_YearMonth={"$(=Date(MonthStart(Today(),-1),'YYYY-MM'))"}
I agree Omar's suggestion should work. Qlik Sense does some weird things with dates though since its stored as a dual (number and string). If it does not correctly identify your link_YearMonth as a date field it may still not work as expected.
The "easiest" way is to set a flag in the load script for LastMonth. I've created a field in load script that is MonthsFromCurrent to allow selection of last month or 2 months ago or 12 months, etc.that is in our standard calendar to allow for selection of any prior month (or period).
Both work fine. Thank you guys.