topic Re: Filter Rolling Year based on in Qlik NPrinting

Filter Rolling Year Nprinting

febridwi — Tue, 26 Mar 2019 06:17:15 GMT

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.

Re: Filter Rolling Year based on

mdmukramali — Tue, 26 Mar 2019 05:11:43 GMT

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

Lech_Miszkiewicz — Tue, 26 Mar 2019 05:26:57 GMT

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

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

Re: Filter Rolling Year based on

Ruggero_Piccoli — Tue, 26 Mar 2019 11:00:23 GMT

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.

Best Regards,

Ruggero

Re: Filter Rolling Year based on

febridwi — Thu, 28 Mar 2019 04:06:47 GMT

@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

Lech_Miszkiewicz — Thu, 28 Mar 2019 04:10:54 GMT

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)

Re: Filter Rolling Year based on

febridwi — Thu, 28 Mar 2019 04:38:15 GMT

@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

Lech_Miszkiewicz — Thu, 28 Mar 2019 04:44:17 GMT

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?

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-and-duals/

cheers

Lech

Re: Filter Rolling Year based on

Lech_Miszkiewicz — Thu, 28 Mar 2019 05:25:39 GMT

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

Re: Filter Rolling Year based on

febridwi — Thu, 28 Mar 2019 07:25:25 GMT

@Lech_Miszkiewicz might be this can be help

Re: Filter Rolling Year based on

Lech_Miszkiewicz — Thu, 28 Mar 2019 07:47:11 GMT

You have not created this flag in your calendar. Or at least i cannot see it there. When i said “Flag” i meant additional field in data load editor/script where you would put the expression i gave you to create it

Re: Filter Rolling Year based on

febridwi — Thu, 28 Mar 2019 08:58:16 GMT

@Lech_Miszkiewicz Can do it without using a "Flag"? What's your opinion then, it will be major change the data. And I don't get what should I make as a "Flag". Is it possible to edit my prev filter ?

Re: Filter Rolling Year based on

Lech_Miszkiewicz — Thu, 28 Mar 2019 09:56:10 GMT

It is very minor change to datamodel and will not affect in any way your current application.
If you have issues understanding how to do it i suggest you ask Qlik Partner or Qlik directly for consultancy day. They will be able to do it in 5 minutes as it is very basic thing to do and it is a very common practice to create flags in calendar for such purpose like yours