Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
paolo_cordini
Partner - Creator
Partner - Creator

Cross Level Calculation in Pixel Perfect

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

Labels (2)
2 Solutions

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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 

Lech_Miszkiewicz_1-1641424996178.gif

 

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

paolo_cordini
Partner - Creator
Partner - Creator
Author

Got it! I had another field in the dimension_level tables, for sorting purposes. I have to disregard this field too in the formula.

Now it works!
Thank you a lot.

regards

Paolo

 

View solution in original post

11 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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.
paolo_cordini
Partner - Creator
Partner - Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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.
Frank_S
Support
Support

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

Please remember hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Lech_Miszkiewicz_0-1641421926946.png

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 🙂

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.
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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 

Lech_Miszkiewicz_1-1641424996178.gif

 

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.
paolo_cordini
Partner - Creator
Partner - Creator
Author

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.

2022-01-06_19h35_22.png

I keep trying

Thank you again

Paolo

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

Lech_Miszkiewicz_0-1641509505642.png

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

Lech_Miszkiewicz_1-1641509708747.png

 

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

 

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.
paolo_cordini
Partner - Creator
Partner - Creator
Author

Hi @Lech_Miszkiewicz 

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

2022-01-07_11h30_54.png

Simulating levels loop... still OK in Qlik Sense

2022-01-07_11h46_34.png

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

2022-01-07_11h34_53.png

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

2022-01-07_11h34_39.png

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

2022-01-07_11h41_45.png

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

Thank You

Paolo