Cross Level Calculation in Pixel Perfect

[paolo_cordini]

Hello 

I have a Pixel Perfect report, which is called from the On Demand Qlik Sense Button.
Users can select one or more document numbers before generating the report.

In the report, I have a "document" level, so the result should be as in the following example

Document 1

Row	Type	Qty	Type Qty
1	A	10	42
2	A	20	42
3	B	5	25

Document 2

Row	Type	Qty	Type Qty
1	A	12	42
2	B	20	25

Footer

Type	Type Qty
A	42 (10+20+12)
B	25 (20+5)

The problem is that the column "Type Qty" is calculated on the total (Document1+Document2) qty value of "Type" dimension. But when processing the levels, a single document is selected, so the total value is not known.

I cannot use a Qlik formula here because the base (total) value depends on which documents are selected by the user before creating the report.

I tried in several ways using calculated fields in Pixel Perfect template, trying to use the "Footer" values, but with no result (I always obtain a blank)

Any suggestion?

Thank You

Best Regards

Paolo

[Lech_Miszkiewicz]

Hi,

I am not sure how the report needs to look like. What confuses me is the bit which you put in the "Footer" section.  To me it is just another detail report outside of the level which loops through documents. So you have your Document Level and Summary Level.

Having more detail regarding how the page needs to look like, whether summary must be on each page or at the end of the report only would also help.

cheers

Lech

[paolo_cordini]

Hi, 

You are right, maybe I oversimplified my example.

Actually, the "Footer" (summary is the right term, sorry) section is not an issue. I have put it in my example just to  show that, at summmary level, the values I have to put in the detail section are correctly displayed, because they are out of the document loop.

My issue is that I have to put these values in the detail section as well (the blue numbers)

Thank you again

Regards

Paolo

[Lech_Miszkiewicz]

Ok - understand. Indeed tough one. I will let you know if I come up with something, but so far - no luck.

cheers

[Frank_S]

Hello @paolo_cordini

I believe this is what you are looking for and have attached sample output, an exported copy of the report and the source QVF file.

(NOTE: Keep in mind that this report was created with NP May 2021 SR 2. You can use import it and use it as a guide to create your own report but may not work fully with your version of NPrinting.)

First

• You'll need to embed at least 1 sublevel. This example embeds 2 sub-levels off the 'Year' dimension level
• Right click and 'Insert detailed reports' for each corresponding level
• Manually type your column headers in 'Group Header 1' using the label box tool on the right side of the template editor. Use Text boxes to describe the data where needed.
• Drag your data fields in as needed
  • Notice the 'repeat every page' group header option to ensure column headers repeat on each page where tables expand over multiple sheets.

Next

• Create two group footers and one report footer exactly as shown above then drag in your data fields into the corresponding footers

Finally, Summing

• Click on the data cell you wish to format (this must be done one cell at a time)
• Using the 'right arrow' properties tool for the data cell, you will need to select your format type using 'format' string and use 'summarize data' using 'group' option for group footers and 'report' opton for the report footer

Save and Preview the report

• The final page of this multi page report output appears as shown below

Kind regards...

[Lech_Miszkiewicz]

Hi @Frank_S 

I think we both fall under the same trap with this topic. I thought the problem was to calculate totals in the footer which is not a case.

The problem is to calculate values framed below in green frames. Since those values come as dimension "[Type]" total and are placed in each row of the report they will not calculate properly when table sits in Level for document, because for Document 1 it will calculate as 30 (10+20 from blue frame on screenshot below) and for Document 2 it will calculate as 12.

If it was all in single table in Qlik Sense there is no problem as you can use Qlik Set analysis to calculate that, but having it split in sections for Document 1 and Document 2 in the report makes it practically impossible...

but...

practically impossible does not mean impossible.

i think I just have a solution and will post it in separate comment as I just came up with it 🙂

[Lech_Miszkiewicz · Accepted solution]

Ok @paolo_cordini 

So here is my solution.

• Create another field in your data model which will be simply Document field loaded as a column with different name, let's call it [Document_Level] 
• Create a measure [Type Qty] with the expression: 
  • If(Sum(Qty)>0 ,Sum(Total <Type> {<Document_Level=>}Qty))
  • as you can see this measures ignores [Document_Level] field in set analysis and calculates Total of Qty for [Type]
• In Pixel Perfect report use [Document_Level field] in your Level
• in Qlik Sense app though allow users apply selection only on [Document] Field

That's it.

Attached are:

• Sample Qlik Sense app
• NPrinting Template 
• .gif with results 

[paolo_cordini]

Hi @Lech_Miszkiewicz 

You are right, your solution should work!

Unfortunately, so far, I couldn't get it working. I'm struggling with the real case, which is of course more complex. The main difference is that I have one more nested level, so I disregarded both dimension levels in the formula.

Everything is working in the Sense app, but in the Nprinting report it is not. The formula does not disregard the dimensions.

I keep trying

Thank you again

Paolo

[Lech_Miszkiewicz]

Hi @paolo_cordini 

It works for me - I have just added another level and it still behaves ok.

Obviously that another extra level again needs to be based on field you are not applying selections on as user in UI which are also the dimensions used in levels must be the one you are disregarding in set analysis. 

I dont know what else I can do - concept is identical to the one I have presented in my previous post.

I work of simplistic concept here but principles are the same - so there is something you are doing differently. Level 1 is in red, level 2 in blue and purple is the measure we discussed.

This is Qlik app with selections applied and OnDemand report generated with results next to it. Everything works as expected.

I dont need to remind about regenerating metadata and re-adding all nodes to levels and sublevels after each change - I am sure you know that.

cheers

[paolo_cordini]

Hi @Lech_Miszkiewicz 

This is my case. The formula is identical to your one.

Simulating levels loop... still OK in Qlik Sense

This is the template. I did it from scratch, identical to yours .

And this is the result. As you can see, the level is not disregarded.

Other differences are:

- the "Row" field is in my case unique across the dimensions

- In this particular example, for one value of Dimension there is only one possible value of Dimension_2

- Some values of "Type" could exist only for one value of "Dimension" and "Dimension_2"

- User selects values only from "Dimension_2"

But I  modified your sample to reflect these differences and it keeps working....

I know, there must be a difference somewhere, but I didn't catch it yet...

Thank You

Paolo