Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Qiyanlu66
Partner - Contributor III
Partner - Contributor III

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

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.

 

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi

my  apologies i should say "evaluate value" as we talking about formula here

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

19 Replies
Frank_S
Support
Support

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

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Qiyanlu66
Partner - Contributor III
Partner - Contributor III
Author

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
Support
Support

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.

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Qiyanlu66
Partner - Contributor III
Partner - Contributor III
Author

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
Support
Support

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

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Qiyanlu66
Partner - Contributor III
Partner - Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Qiyanlu66
Partner - Contributor III
Partner - Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.