topic Dynamic referencing Excel report in Qlik NPrinting

Dynamic referencing Excel report

Danijel — Mon, 08 Feb 2021 15:55:55 GMT

Hello all,

I am currently building an NPrinting report in Excel. My current problem is to access different totals.

In my example, I am trying to access a value (Total A) from the top section and offset it in the bottom section, which works. However, the whole thing doesn't work on Total B and C anymore, since NPrinting doesn't access the Totals, but only moves the number of rows down.

Is there any way to reference this dynamically? Meaning that I always access the respective total row in the formula?

Designer

Result

Re: Dynamic referencing Excel report

Lech_Miszkiewicz — Mon, 08 Feb 2021 21:43:11 GMT

Hi - sorry I dont understand your question well so I will just try to guess it...

If your goal is to be able to reference the same formula for total in bottom table and then somehow dynamically pick for that formula values from row in the top table i would suggest to use some sort of reference functions which would be able to reference Total A or Total B... you may need to consider VLOOKUP to search for correct total line and not reference in your formulas single cell but range. That way from range only VLOOK'ed-UP value could be used...or you may use SUMIF() or similar functions..

Re: Dynamic referencing Excel report

Lech_Miszkiewicz — Mon, 08 Feb 2021 22:25:13 GMT

so.. based on your template screenshot I would do something like below (assuming that Totals in column B have the same labels between top and bottom section):

IF((E18+F18+L18)>0;SUMIF(B6:B10,B18,G6:G10)/((E18+F18+L18)/C18);"N/A")

Re: Dynamic referencing Excel report

Danijel — Tue, 09 Feb 2021 10:29:18 GMT

Hi @le ,

thank you for your answer. Sorry, unfortunately I do not really know how to describe the problem.

However, your idea and interpretation with the sumif was not bad.

And yes the areas A B and C are identical above and below.

I tried to implement that, however NPrinting shifts the areas (see screenshot) awkwardly. Here I worry that the range times values will not catch or use the values from below, which it should not do.

Re: Dynamic referencing Excel report

Lech_Miszkiewicz — Tue, 09 Feb 2021 11:34:50 GMT

I understand that NPrinitng may shift records - this is dependent on how you wrap your tags and levels together. You need to remember that rows where level tags are placed may cause that shift as they will disappear during generation process. I suggest you practice this first... Place where you have levels, places where you have <deleterow> tags... all that needs to be taken into account for your range to remain in place. I understand that this app has some real data in it hence you cannot use it, but if you build a dummy with Qlik Sense Ctrl +0+0 script and some NPrinitng report of that then we can have working sample which I would be able to guide you through. Without the actual example it will be hard to explain it

Re: Dynamic referencing Excel report

Danijel — Tue, 09 Feb 2021 13:02:46 GMT

Hi @Lech_Miszkiewicz ,

I have built a small example for you, which reflects the problem quite well.

Hope it helps you.

Thank you.

Re: Dynamic referencing Excel report

Lech_Miszkiewicz — Tue, 09 Feb 2021 21:26:03 GMT

That was simpler than i thought.... I did not even think that you missed $ sign in your excel formulas which is needed to keep the range and not allow to drag it down with the row references....

If you were unaware of it here is simple explanation: https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9

you can see on template that i am creating sum (based on SumIf() concept) for cell marked in yellow and on another picture is a result. I also attached the nprinting template export.

cheers

Re: Dynamic referencing Excel report

Danijel — Wed, 10 Feb 2021 06:25:10 GMT

Hi @Lech_Miszkiewicz

yes it was, but easier than I thought, but you have to get the idea first 🙂

Thanks for your help.

Greetings