Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rsapaull
Contributor III
Contributor III

Creating filter for previous months in the year

Effectively what I am trying to do is create a flag in the load script similar to using the InYearToDate function for a YTD option, however this only works if you are looking at the current year and I have graphs that show CY and prior years.

So for example, let's say it is May, I would like a filter pane where I can have everything with a date where the month is January, February, March or April to be selected, regardless of the year for one value (e.g. -1) and then everything else is another value.  The field needs to be dynamic and I can't simply create a month filter and select these manually.

I have used the below formula to do this to create year to date, but it doesn't work if the graph has multiple years.  Any ideas?

InYearToDate(Renewal_Date,(monthend(addmonths(Today(),-1))),0)*-1 as YTD

Labels (1)
1 Solution

Accepted Solutions
rsapaull
Contributor III
Contributor III
Author

For anybody else looking at this post, the solution as an actual formula is as follows:

IF((Num(Month(Renewal_Date)))<(Num(Month(Today()))),1,0) as MTD,

View solution in original post

2 Replies
srdheekonda
Contributor III
Contributor III

If i understood your question correctly, Create a flag column by taking the month number from your date column as well as dynamic current date by checking following condition IF DateMonthNumber<Today's Month Number THEN 1 ELSE 0

As per above scenario -- In the Month of May -- Today's date month number will be 5 and all the dates from Jan to April fall less than 4 month number and it will group to 1 bucket.

One point to be noted is in case of incremental load we need to take care of previous days data loaded for closed month when we are moving to new month to avoid any issues with the flag. I hope this helps

rsapaull
Contributor III
Contributor III
Author

For anybody else looking at this post, the solution as an actual formula is as follows:

IF((Num(Month(Renewal_Date)))<(Num(Month(Today()))),1,0) as MTD,