Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Partner
Partner

Filter Rolling Year Nprinting

Hi Everyone,

Now I'm running on Nprinting project. And I got filter change than before, How set filter for 'rolling year', the scenario is : if today is Mar 2019, Nprinting must generate report from Jan 2018 - Feb 2019. and the scenario will able for next month. 

Currently we used 2 filter, there are:

Year: =If(Month(Today())>1, Year(Today()), Year(AddYears(Today(), -1))) 

Month: =Month(AddMonths(Date(Today()),-1))

But with these filters, NPrinting generated report only Feb 2019 , not from Jan 2018 - Feb 2019. 

 

Does anyone have an idea? What should we replace in the previous filter?

 

Have a good day,Febri.

 

 

12 Replies
Highlighted
mdmukramali
Valued Contributor III

Re: Filter Rolling Year based on

Hi,

Yes, it will generate the reports for Feb-2019.
because

Year: =If(Month(Today())>1, Year(Today()), Year(AddYears(Today(), -1)))  It will Return 2019 Year

Month: =Month(AddMonths(Date(Today()),-1)) it will return Feb Month

 

you have to change the Condition. in the NPrinting Filter i have no idea how we can change.

but in the report, you can use Set Analysis to achieve it.

 

Thanks,

Mohammed Mukram 

 

Re: Filter Rolling Year based on

Hi,

The best way to approach this is by creating a flag in your Qlik datamodel.

I am not 100% sure what range do you want to do and what do you mean by rolling year. Maybe give us some scenarios....but to give you an idea how it can work i would refer directly to  date field: 

If(Date>=YearStart(Today()) and Date <MonthStart(Today()),1,0) as flag_Rolling_Current_Year, - this will flag all dates in your model starting from beginning of the year till end of last month

or

If(Date>=YearStart(Today(),-1) and Date <MonthStart(Today()),1,0) as flag_Rolling_CurrentNPrevious_Year, - this will flag all dates in your model starting from beginning of the last year till end of last month

then in NPrinitng you would just use 1 filter - flag you would create (so your filter would be like: flag_Rolling_Current_Year= numeric value is: 1

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

Re: Filter Rolling Year based on

Hi,

Advanced filtering is not yet available on Qlik NPrinting (February 2019) so, in my opinion, the best solution is the one suggested by Lech. This solution has also the advantage that formulas in the reload script are calculated one time during data reload and not every time a filter is applied to generate a report. This made report generation faster. 

As general rule to create a OR condition (you want to select sales of today OR yesterday OR...) you have to insert multiple lines, one for each value, in the filter field like in the picture below.

15 days.png

Best Regards,

Ruggero

 



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
Partner
Partner

Re: Filter Rolling Year based on

@Ruggero_Piccoli thanks for the advice. By the way, what's the expected result if we apply the filter like that? 

Thank you

Re: Filter Rolling Year based on

Ruggero just shown you how "Or" condition is applied and it is will not be useful in your scenario.

His example would return Today, Yesterday,2 and 3 days back (4 days all together)

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

Re: Filter Rolling Year based on

@Lech_Miszkiewicz i have tried your suggestion on Qlikview doc (both) but when I check the result is 0. Should I check untill I implement to NPrinting?

Re: Filter Rolling Year based on

Check in QlikView if your formula works directly there, if it does please screenshot your filter in NPrinting and attach here (just like Ruggero did)

Did you use "numerical value is" = 1?

1.png

I mentioned here about traps with datatypes when using NPrinting filters:

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

cheers

Lech

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.

Re: Filter Rolling Year based on

Hi 

I probably misunderstood your previous post. So if it is not working for you in QlikView script:

  • please screenshot your script where you have applied it,
  • please also screenshot table viewer after reload (so we can see data model and existing connections)
  • please hover mouse cursor over the field in data model viewer so we can see what datatype was allocated to your date field.

You need to make sure that your date is actually stored as date (not as a text) to ensure that formula i suggested works for you

cheers

Lech

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

Re: Filter Rolling Year based on

@Lech_Miszkiewicz might be this can be help

Qlikview variables show Full.png

Table viewer Date type.png