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

[dineshm030]

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 

[Lech_Miszkiewicz]

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

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

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

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

[dineshm030]

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.

[Lech_Miszkiewicz]

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

[dineshm030]

Hi @Lech_Miszkiewicz 

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

[dineshm030]

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?

[Lech_Miszkiewicz · Accepted solution]

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.

[Lech_Miszkiewicz]

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)

[dineshm030]

It is working @Lech_Miszkiewicz.

Thank you so much for your assistance.