Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
dineshm030
Creator II
Creator II

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

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 

1 Solution

Accepted Solutions
Lech_Miszkiewicz

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:

Lech_Miszkiewicz_0-1643966672329.png

 

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_1-1643967430234.gif

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

8 Replies
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...

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
dineshm030
Creator II
Creator II
Author

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.

dineshm030_0-1643963318453.png

 

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
dineshm030
Creator II
Creator II
Author

Hi @Lech_Miszkiewicz 

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

dineshm030_0-1643964940958.png

 

dineshm030
Creator II
Creator II
Author

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?

dineshm030_0-1643966698273.png

 

Lech_Miszkiewicz

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:

Lech_Miszkiewicz_0-1643966672329.png

 

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_1-1643967430234.gif

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
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)
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
dineshm030
Creator II
Creator II
Author

It is working @Lech_Miszkiewicz.

Thank you so much for your assistance.