How to get last 12 months of data by using filter?

[Qiyanlu66]

Hello everyone,

If I want to get last 12 months of data of vCurDate,

is there any way I can do this? I am not sure how to set up the range of date in the filter.

Thank you very much.

[Frank_S]

Hi @Qiyanlu66 

If I understand your requirement correctly, you should be able to setup a single filter using your 'Month' dimension with the following dimension values:

See the following link for creating dynamic value filters.  https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ReportsDevelopment/Static-dynamic-...

Month(now())

Month(now())-1

Month(now())-2

Month(now())-3

Month(now())-4

Month(now())-5

Month(now())-6

Month(now())-7

Month(now())-8

Month(now())-9

Month(now())-10

Month(now())-11

Hope this helps...

[Qiyanlu66]

Hello Frank,

That's what I thought at the beginning, but then I realize  I need to wrong about the year as well. 

If I do that , I believe the results will be all the data for every years, but not only the data of prev 12 month.

[Frank_S]

Try adding another formula with the rolling years (or specific years) you want to report on.

Year(now())

Year(now())-1 etc.

Just a thought.

[Qiyanlu66]

If I understand correctly, for example, what you mean is

If the data contains from Jan/2014 to Nov/2017

If vCurDate is May/2016

Then I put the following in my filter:

Month(vCurDate )

Month(vCurDate )-1

Month(vCurDate )-2

....

Month(vCurDate )-11

Year(vCurDate)

Year(vCurDate ) -1

I believe it will return me all the data for 2015 to 2016?

I would exactly want the data from May/2016 to May/2017

[Frank_S]

Hi @Qiyanlu66 

I am not certain what your requirement is. Some combination of what I suggested may work for you but you need to try a few things before you get the result you need. 

Someone else here may be able to better understand what you need an provide a response. 

best wishes...

[Qiyanlu66]

Uhh, let me put this in another way, is this possible to use less than or greater than in filter expression? 

Such that get last 6 months data by  >= (date(Addmonths(vCurDate, -6)))

[Lech_Miszkiewicz]

No it is not possible to use >= <=

1. The best approach is to do ">= <=" calculation in the qlik load script and create a flag. Then use this flag as Nprinting Filter. 

2. Another option is to have Date(Floor(MonthStart(yourdatefield)),’MMM-YY’) as MonthYear field created and then you can follow Franks suggestion to create 12 “monthstarts” by using AddMonth function in a single NPrinting Filter like;

Filter:

Field: MonthYear,  Numerical value is: Floor(AddMonths(monthstart(Today()),0)) ->(current mont)

Field: MonthYear,  Numerical value is: Floor(AddMonths(monthstart(Today()),-1)) ->(last month mont)

Field: MonthYear,  Numerical value is: Floor(AddMonths(monthstart(Today()), -2))

....

Field: MonthYear,  Numerical value is: Floor(AddMonths(monthstart(Today()),-12)) ->(12 months back)

Using only Month and Year Fields will always create confusion. You need to explicitly relate to Month/Year column derived from date.

hope this helps

cheers

Lech

[Qiyanlu66]

Hello Lech,

We exactly have a MonthYear field created, and I tried your solution and some other posts on the forum but it seems hard to match the format of our MonthYear field. We have some thing like 2010-Jan, 2010-Feb .....

I have tried the following code in the filter but still cannot get it work.

=(Date(Floor(AddMonths(monthstart(vCurDate),-1))))

=(AddMonths(vCurDate, -1))

=(Date(AddMonths(vCurDate, -1), 'YYYY-MM' ))

=(text(Date(AddMonths(vCurDate, -1), 'YYYY-MM' )))

Do you have any idea of whats goes wrong? 

Thank you very much!

[Lech_Miszkiewicz]

Well,

none of your format suggestions is right. If you only look at the code i sugested .... It clearly says Floor() at the very beginning..and Numerical value is..

This is because Qlik date formats are stored as dual an NPrinting needs to use numerical representation to apply filter. Floor function makes sure your MonthStart() is stored as integer (number).

That is why i said to create filter where:

Numerical value is: Floor(AddMonths(monthstart(Today()),0))

"Numerical value is" - this apears in NPrinting filter dropdown...

more about filter pitfalls you can read on my blog here:

https://nprintingadventures.wordpress.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-...