Qlik Community

Ask a Question

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

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

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

We are just 'like' you and like to be liked when providing a helpful answer. You may also press the 'Solution Accepted' button if an answer provided resolves your question or issue... Cheers!
Partner
Partner

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.

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.

We are just 'like' you and like to be liked when providing a helpful answer. You may also press the 'Solution Accepted' button if an answer provided resolves your question or issue... Cheers!
Partner
Partner

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

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

We are just 'like' you and like to be liked when providing a helpful answer. You may also press the 'Solution Accepted' button if an answer provided resolves your question or issue... Cheers!
Partner
Partner

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

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

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!

 

 

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.