topic Re: Date Filter for previous month not working in Qlik NPrinting

Date Filter for previous month not working

Thabang231 — Wed, 04 Sep 2024 08:39:55 GMT

Hi Team,

I have been trying to create the filter to pull the previous month data only from the date field formatted like this : 2024-09-02 02:41:11.787 , but i am not winning i tried multiple solutions and getting errors, kindly assist on which logic/formula should i use.

Thanks in Advance.

Re: Date Filter for previous month not working

madelonjansen — Wed, 04 Sep 2024 13:03:50 GMT

Your date field is actually a time-stamp.

I would suggest to build a calendar table, based only on the dates (not the times) in this field.
You can make a 'real' date field by using
Date(Floor([TimestampfField])) as Date

Then create a calendar table that references it. I personally like this one:
https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

After you can use a Month or MonthNum or even a YYYYMM to reference the current and previous month.

An alternative method, without using a Calendar table would be to add a field like this:
if(MonthName([Timestamp]) = Monthname(AddMonths(Today(), -1)), 1, 0) as PreviousMonth.
All of the Timestamps in the previous month will have a 1 in this field.

Re: Date Filter for previous month not working

David_Friend — Fri, 06 Sep 2024 17:38:06 GMT

@Thabang231 if the suggestion from @madelonjansen helped you please click on 'Accept as Solution'

Re: Date Filter for previous month not working

Lech_Miszkiewicz — Sun, 08 Sep 2024 15:04:45 GMT

Hi @Thabang231

I agree with @madelonjansen and @David_Friend.

Besides if you need more info read those:

There are also existing topics on community which answer the same question:

https://community.qlik.com/t5/Qlik-NPrinting/Filter-Previous-month-Nprinintg/td-p/1798789

Bottom line is that you need to understand:

what data type of the field you are working with
whether you are willing to use best practices and create typical date field or even proper calendar (!!! not autocalendar using derived fields!!!)
depending on above you may be using "advanced search" or "evaluate value" NPrinting filter types as only those will work if used properly in this scenario.

cheers