Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

morenoju
Contributor II

YearMonth in Set Analysis

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

Tags (2)
1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: YearMonth in Set Analysis

try it like this

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

4 Replies
OmarBenSalem
Esteemed Contributor

Re: YearMonth in Set Analysis

try it like this

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

Re: YearMonth in Set Analysis

I would suggest to change AddMonths() to MonthStart here

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

dwforest
Valued Contributor

Re: YearMonth in Set Analysis

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
Contributor II

Re: YearMonth in Set Analysis

Both work fine. Thank you guys.

Community Browser