topic Extract the data for Current Month and Previous Month by NPrinting filter in Qlik NPrinting

Extract the data for Current Month and Previous Month by NPrinting filter

dineshm030 — Fri, 04 Feb 2022 07:58:03 GMT

Hi All,

I have created one tabular report in QlikSense which has Year, Month, Name, Measures.

I want to extract the two months of data from the Qlik Nprinting Report and save it as an excel file.

I need to filter two months of data, if I am in Jan 2022, the data should be stored Jan 2022 and Dec 2021 data in the file.

I am using the Yearmonth field (YYYYMM).

Kinldy provide the syntax for extraxting the two months data

Thanks in advance.

@sunny_talwar @MayilVahanan

Re: Extract the data for Current Month and Previous Month by NPrinting filter

Lech_Miszkiewicz — Fri, 04 Feb 2022 05:58:29 GMT

HI @dineshm030

Please provide a syntax of how you create your YYYYMM field in Qlik script as it is critical to know whether it is created as: text, number, date/dual.

The typical way of creating it would be similar to:

Date(Floor(MonthStart(YourDate)),'YYYYMM')

If the above is true you could simply use Evalueate filter like:

MonthYear = Evaluate value = Num(Floor(MonthStart(Today()) - for current month
MonthYear = Evaluate value = Num(Floor(MonthStart(Today(),-1) - for last month

Or you could use advanced search as described in my post linked below

MonthYear =([MonthYear]>=MonthStart(Today(),-1))*([MonthYear]<=MonthStart(Today()))

I have explained importance of that (of verifying what is the type of data we are trying to filter) in my post about typical mistakes when working with filters here:

https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/

you may find this usefull for your purpose:

https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/

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

and as always there is plenty of context on community covering exactly the same topic:

https://community.qlik.com/t5/Qlik-NPrinting-Discussions/FILTER-CURRENT-MONTH/td-p/1804558

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

https://community.qlik.com/t5/Qlik-NPrinting-Discussions/How-to-set-up-a-filter-quot-Last-X-days-quot-in-NPrinting/td-p/1203955

at the end

always start by studying qlik help site which covers all those questions:

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

Re: Extract the data for Current Month and Previous Month by NPrinting filter

dineshm030 — Fri, 04 Feb 2022 08:30:36 GMT

Hi @Lech_Miszkiewicz

I am facing the issue below filters.

I want to filter based on the Yearmonth column (YYYYMM). I need to extract two months data.

Please do the needful.

Re: Extract the data for Current Month and Previous Month by NPrinting filter

Lech_Miszkiewicz — Fri, 04 Feb 2022 08:41:32 GMT

I repeat. How do you create your Yearmonth field in qlik sense script?

we must know that to give you correct answer as we must know if it is date, dual, text or number

Re: Extract the data for Current Month and Previous Month by NPrinting filter

dineshm030 — Fri, 04 Feb 2022 08:56:51 GMT

Hi @Lech_Miszkiewicz

it's coming as a Number. When I put Max(Yearmonth) it returns as 202201.

Re: Extract the data for Current Month and Previous Month by NPrinting filter

dineshm030 — Fri, 04 Feb 2022 09:26:30 GMT

Hi @Lech_Miszkiewicz

When I filter with maximum yearmonth. I get maximum year month data in that report.

how do we filter for two months?

Re: Extract the data for Current Month and Previous Month by NPrinting filter

Lech_Miszkiewicz — Fri, 04 Feb 2022 09:37:47 GMT

Hi @dineshm030

Well that still can be a date with YYYYMM mask on it. That is the reason why I ask to provide the script loading that field....

But if you insist that it is a number then we have to work around it little bit as we have to translate:

date to text
then text to number

So for example if your 202201 is actually a number value of two hundred twenty two thousand and one and its type in Qlik Sense is numeric then you would have to do:

Yearmonth

Values:

Evaluate value = num#(Text(Date(Today(),'YYYYMM')))
Evaluate value = num#(Text(Date(AddMonths(Today(),-1),'YYYYMM')))

This will only work if what you are saying is true and the yearmonth is not actual year month value but simply a number.

Explanation:

Providing load script statement for that field/and or preview of the actual data type from Qlik Sense model viewer would clarify all doubts. As you can see from gif below all 3 fields look the same but all of them would have to have differently constructed filter because they are of different data type.

Re: Extract the data for Current Month and Previous Month by NPrinting filter

Lech_Miszkiewicz — Fri, 04 Feb 2022 09:53:25 GMT

So just like on my screenshot you just create 2 values and if you are using Max() function then you could use:

Evaluate value = Max(Yearmonth)
Evaluate value = Max(Yearmonth,2) (second parameter is a rank which will give you second max value)

Re: Extract the data for Current Month and Previous Month by NPrinting filter

dineshm030 — Fri, 04 Feb 2022 13:16:27 GMT

It is working @Lech_Miszkiewicz.

Thank you so much for your assistance.