Latest 2 months data

[Uri]

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

[Andrea_Bertazzo]

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

[Lech_Miszkiewicz]

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

[Uri]

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

[Ruggero_Piccoli]

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

[Uri]

Hi @Ruggero_Piccoli ,

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

BR,

Artur

[Lech_Miszkiewicz · Accepted solution]

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" ???

[Uri]

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

[Lech_Miszkiewicz]

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

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

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