topic Re: Latest 2 months data in Qlik NPrinting

Latest 2 months data

Uri — Fri, 15 Nov 2024 23:10:16 GMT

Hi,

I have a field YearMonth (format: jan. 2021).

Today Im using =YearMonth=max(total YearMonth) to get the latest YearMonth when I open the app.

I want to use a filter in NPrinting to show last 2 months data.

How can I do this?

BR,

Artur

Re: Latest 2 months data

Andrea_Bertazzo — Wed, 10 Feb 2021 16:46:02 GMT

Hi Artur,

you can add a new value in the same filter and add the previous month.

The formula could be =YearMonth=max(total YearMonth)-1 but this depends on how you defined the YearMonth field

Re: Latest 2 months data

Lech_Miszkiewicz — Wed, 10 Feb 2021 22:06:51 GMT

Here you have short scenarios and things you have to be aware of when working with Advanced Searches and also When working with dates...

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

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

landing page: https://nprintingadventures.com

Re: Latest 2 months data

Uri — Thu, 11 Feb 2021 14:38:55 GMT

Hi guys,

YearMonth defined as MonthName(dat) as YearMonth.

I already tried like =YearMonth=max(total YearMonth)-1 and it didn't work.

BR,

Artur

Re: Latest 2 months data

Ruggero_Piccoli — Thu, 11 Feb 2021 15:18:45 GMT

Hi,

Could be a problem due to the dual representation of data. Try to force to Text().

Remember also that when you use formulas in filters, you have to select Evaluate value from the dropdown menu.

Best Regards,

Ruggero

Re: Latest 2 months data

Uri — Thu, 11 Feb 2021 16:57:09 GMT

Hi @Ruggero_Piccoli ,

it doesn't work with text or num either...

BR,

Artur

Re: Latest 2 months data

Lech_Miszkiewicz — Thu, 11 Feb 2021 20:55:50 GMT

Hi @Uri

Have you gone through the links I have sent in my previous post? So the first question is:

how is you YearMonth field created in your load script?
- is it stored in Qlik as text or number
I assume it is actually created as Date(MonthStart(YourDate),'MMM.YYYY') or similar which is still a date!!!! grouped as 1st day of month and with Month.Year format
- If that is the case then your filter can be created in 2 ways:
  - by creating filter to select current and last month (with Evaluate value option)
    - if your current filter works for current month =YearMonth=max(total YearMonth)
    - then for last month I would do something like this: =YearMonth=AddMonths(max(total YearMonth),-1)
  - by creating range using advanced search

Understanding how is your calendar built as well as if there is association with date field could help as well as I would then use different formulas to select your month year field. I would use this option:

current month: =Only({<SomeDateField={'$(=Today())'}>}MonthYear)
last month: =Only({<SomeDateField={'$(=AddMonths(Today(),-1))'}>}MonthYear)

There is no point talking about filters if we dont know how is your calendar built. It is not really NPrinitng question - it is Qlik Sense question.

... or maybe you are using autogenerated calendar from Qlik Sense by using "derived fileds" ???

Re: Latest 2 months data

Uri — Thu, 11 Feb 2021 21:43:50 GMT

Hi @Lech_Miszkiewicz

=AddMonths(max(total YearMonth),-1) did the trick.

My YearMonth field looks like this:

MonthName(dat) as YearMonth and stored like (dec. 2020). I will go through your links since I'm still not getting why text() didn't work... I really appreciate your help guys.

BR,

Artur

Re: Latest 2 months data

Lech_Miszkiewicz — Fri, 12 Feb 2021 03:40:49 GMT

Text did not work because your YearMonth field is not text field!!!

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/monthname.htm

You always need to know what data type you are working with... Since it is created by using monthname function it is actually dual field which contains numerical representation and text sumbol of it. For all dual values NPrinting is requiring numerical filter which is documented here:

https://help.qlik.com/en-US/nprinting/February2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm#anchor-5

I also mention all this in one of my blog topics "Pitfalls of NPrinitng filters dates and duals...."