YearMonth in Set Analysis

[morenoju]

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

[OmarBenSalem · Accepted solution]

try it like this

link_YearMonth={"$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}

[sunny_talwar]

I would suggest to change AddMonths() to MonthStart here

link_YearMonth={"$(=Date(MonthStart(Today(),-1),'YYYY-MM'))"}

[dwforest]

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).

[morenoju]

Both work fine. Thank you guys.