topic Re: Dynamic Fiscal Year Formula in NPrinting Report in Qlik NPrinting

Dynamic Fiscal Year Formula in NPrinting Report

NishantPatel — Wed, 03 Nov 2021 22:14:43 GMT

Hi,

I have a formula here (see below) which is being used in an NPrinting report.

I would like to make the value "2021-2022" dynamic for the FiscalYear field in this formula by using a variable (similar to the weeks variable I have in the formula).

Is there a way that this can be done?

Thanks in advance.

Re: Dynamic Fiscal Year Formula in NPrinting Report

Frank_S — Wed, 03 Nov 2021 22:35:28 GMT

Here is a suggestion:

Create a variable in your source application (QVW or QVF) using the underlying formula you posted above (make sure it works by testing in your source app first)
Save the QVF or QVW
Reload the NP connection so the variable becomes available
Then Use the variable tool in NPrinting Designer to add your variable to the report.

Perhaps you also or only need an variable filter in NPrinting as well.

For this see:

https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Work-with-variables.htm
https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm#anchor-10 (this explains how to create a variable based filter if thats what you actually need).

Kind regards...

Re: Dynamic Fiscal Year Formula in NPrinting Report

Vegar — Thu, 04 Nov 2021 05:51:11 GMT

I believe @Frank_S 's suggestion covers how to add a new variable in your NPrinting report.

If you have trouble defining the variable, then take a look at the yearname() function. It can be helpful for achieving your desired variable. https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/yearname.htm

Re: Dynamic Fiscal Year Formula in NPrinting Report

NishantPatel — Wed, 17 Nov 2021 03:37:31 GMT

Hi Frank and Vegar,

Thank you for your suggestions.

I already have an existing variable in my source app (QVW) to dynamically calculate the week as per below screenshot.

In the same way I created a new variable in my source app (QVW) to dynamically calculate the fiscal season by using the yearname() function (as suggested by Vegar). The fiscal season starts from 1st August, hence why I used "8" which then returns the appropriate fiscal season (i.e. 2021-2022).

I then applied this new variable (vSeasonTest) into my existing formula in the NPrinting report and ran a preview of the report.

After running a preview of the report, I came across this error message.

Could you please advise?

Thanks

Re: Dynamic Fiscal Year Formula in NPrinting Report

Lech_Miszkiewicz — Wed, 17 Nov 2021 07:36:29 GMT

Hi,

you are using $ sign expansion on your variable vSeasonTest preceded with "=" sign.

which is for example $(=2021-2022)

That means that QlikView will try to calculate that deduction.. and simply throws an error.

My suggestion is to call variable without "=" sign - like $(vSeasonTest) instead of $(=vSeasonTest)
Second thing is to actually wrap it is single quotes as you are doing text search in set analysis
1. ....FiscalYear={'$(vSeasonTest)'}...

Those are couple basic issues I could spot at first glance..

You may ask why does your formula work for Week then since you are using the same $(=variable) approach? It is because of values returned being 2021-2022 for years which Qlik will translate as:

2021-2022=-1

On contrary for week you are likely getting text which cannot be translated to anything else - hence it works. I would however fix your week variable call in set analysis too to avoid further issues.

cheers

Re: Dynamic Fiscal Year Formula in NPrinting Report

NishantPatel — Tue, 23 Nov 2021 22:33:24 GMT

Hi Lech,

Thanks for your solution.

I have been able to get it working.