Skip to main content

Qlik NPrinting

Discussion Board for collaboration on Qlik NPrinting.

cancel
Showing results for 
Search instead for 
Did you mean: 
NishantPatel
Contributor II
Contributor II

Dynamic Fiscal Year Formula in NPrinting Report

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

Dynamic Fiscal Year.jpg

Is there a way that this can be done?

Thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
Lech_Miszkiewicz

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.

  1. My suggestion is to call variable without "=" sign - like $(vSeasonTest) instead of $(=vSeasonTest)
  2. 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

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.

View solution in original post

5 Replies
Frank_S
Support
Support

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:

Kind regards...

We are just 'like' you and like to be liked when providing a helpful answer. You may also press the 'Solution Accepted' button if an answer provided resolves your question or issue... Cheers!
Vegar
MVP
MVP

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

NishantPatel
Contributor II
Contributor II
Author

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.

NishantPatel_1-1637118813743.png

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

NishantPatel_2-1637119136679.png

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

NishantPatel_3-1637119547380.png

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

NishantPatel_4-1637119907390.png

Could you please advise?

Thanks

 

 

 

 

Lech_Miszkiewicz

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.

  1. My suggestion is to call variable without "=" sign - like $(vSeasonTest) instead of $(=vSeasonTest)
  2. 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

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.
NishantPatel
Contributor II
Contributor II
Author

Hi Lech,

Thanks for your solution.

I have been able to get it working.