Date Filter for previous month not working

[Thabang231]

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.

[madelonjansen · Accepted solution]

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.

[David_Friend]

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

[Lech_Miszkiewicz · Accepted solution]

Hi @Thabang231 

I agree with @madelonjansen  and @David_Friend.

Besides if you need more info read those:

• https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/
• https://nprintingadventures.com/2022/03/19/filters-in-nprinting-how-hard-can-it-be-part-3-evaluate-v...

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