Is there a way to apply Year Month filter (For last 6 Months) for a specific chart in a report

[Qlikuser09]

Is there a way to apply Year Month filter (For last 6 Months) for a specific chart in a report in NPrinting

[Frank_S · Accepted solution]

Perhaps you can create an NPrinting filter with your month dimension and use the formula similar to:

Month(now())-6

If you need the last six months you can create five more filter values on the same filter.

Month(now())-5

Month(now())-4

etc

• https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-fi...

Ex: Here is a sample NP filter for last 3 months...

Next

Apply the filter directly to the report object.

For the steps to apply a filter to an object visit the following link:

• https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-fi...

Others here might have better ideas to accomplish the creation of this filter perhaps using advanced search. This is just one way.

Kind regards...

[Lech_Miszkiewicz · Accepted solution]

Hi @Frank_S 

regarding the method how to apply filter to single object - @Frank_S has given you all details. Note that this is only available in MsWord/MsExcel/MsPowerPoint and HTML  and only to certain report objects like tables or images (not levels, variables, formulas or pages).

Regarding the actual filter construction this is a long topic and goes all the way back to your Qlik Sense data model and the field you want to apply your filter on. Also you have to ask yourself a question how this needs to behave when you roll over to next year as Franks example returns only month value so having data for multiple years in data model will still return all data from all months regardless of the year.

so again main question is: how is that field created in your Qlik Sense data model?

So if you have YearMonth field created in typical way in your Qlik Sense script which would be:

• Date(Floor(MonthStart(yourdate)),'MMM-YYYY') as [Month Year]

Where:

• MonthStart groups all dates in the month into single date (1st day of the month)
• Floor() - removes timestamp value from it - not really necessary in this case as month start already gives you time as at 00:00:00
• Date - applies a chosen date/text mask on the numerical value of date

So the formula would actually create a field representing first day of the month in a date format with Month and Year text mask on it. Obviously benefit of having dates constructed like this is that you can apply all date related functions like adding months, years, weeks, deriving day numbers, week numbers etc.... So if your field is created like that in Qlik Sense then your filters could be

• Evaluate value = num(floor(MonthStart(today(),0))) - current month (if you want to include current month)
• Evaluate value = num(floor(MonthStart(today(),-1))) - last month..etc
• Evaluate value = num(floor(MonthStart(today(),-2)))
• Evaluate value = num(floor(MonthStart(today(),-3)))
• Evaluate value = num(floor(MonthStart(today(),-4)))
• Evaluate value = num(floor(MonthStart(today(),-5)))
• Evaluate value = num(floor(MonthStart(today(),-6))) - if you dont include current month and need 6th month

Or

• Advanced search: =([Month Year]>=MonthStart(Today(),-6))*([Month Year]<MonthStart(Today()))  - this will exclude current month as we use "less then '<' option)

Important - I have had scenarios where I had to enforce which value type needed to be applied on the filter and learned that in case of "evaluate value" option I had to add num() to tell NPrinting that filter value returned from formula and passed on Dual field [Month Year] must be in numerical format

If you dont have[Month Year] field and still wish to use simpler filter and maybe have actual date field in your data model you could leverage advanced search option like below:

• Advanced search: =([Date]>=MonthStart(Today(),-6))*([Date]<MonthStart(Today())) 

We have had so many discussions about date filters that the best i can do is to point you to one of those here: https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Unable-to-apply-quot-Year-Month-quot-YYYY-M... where I described the principles of what are date fields in Qlik Sense (or QlikView) and how it affects the way you would apply filter in NPrinting.

I also specifically covered the same topic on my blog post regarding filters (check especially the one about Dates and Duals and Advanced Search):

https://nprintingadventures.com/category/nprinting/filters/

hope this helps

cheers