Solved: "Total" is not visible in NPrinting chart but it i... - Qlik Community

deep2021 · ‎2022-01-11

Hi,

In Qlik Sense chart total is visible but in the n printing excel report it is not visible.

Since we are not using the Top n Values for the dimensions, the count of rows is not the same.

Can you please suggest how to get the total in the NPrinting excel report when the count of rows are not the same?

Thanks

Ruggero_Piccoli · ‎2022-01-11

Hi,

Thanks, that screenshot lets me to understand. You dragged and dropped each single column of the Qlik Sense table into the Excel template, and that is correct but it doesn't add the totals. So with this structure of the template it is correct that totals are not visible.

To add the totals you have to follow step by step the instructions in https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ExcelReports/Make-Calculated-Column..... So create a native Excel table and use Excel formulas to add calculated totals on the bottom.

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

View solution in original post

Lech_Miszkiewicz · ‎2022-01-11

Hi @deep2021

Solution suggested by Ruggero works regardless of number of rows in the table. Here is also the video explaining it: https://www.youtube.com/watch?v=HjIK7cC9HJk

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.

View solution in original post

Ruggero_Piccoli · ‎2022-01-11

Hi @deep2021 ,

Please, read carefully the linked instructions. The Excel sum() formula is entered in the template selecting at least two rows, not only one. Selecting two or more rows lets Qlik NPrinting to understand that it has to expand the formula to include all generated rows also when their number is not fix nor know from the beginning.

The DeleteRow tag is not mandatory but, when added, it indicates to Qlik NPrinting to delete that row from the final report. So you will deliver a report without an empty row between the details and the totals. If you want to keep an empty row you can avoid to add the DeleteRow tag.

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

View solution in original post

Lech_Miszkiewicz · ‎2022-01-18

Hi @deep2021

I guess we can confirm what is possible and what isn;t .

Option 1. ("expand range" turned off)

Tables: Disabled Expand Range (so that would be your original approach)
Total Line (where required) dealt not with Excel, but with the grouping dimension. Concept of grouping dimension was discussed many times so feel free to do your own research on that. Here is a sample how you can achieve it:
Charts with Properties option set to: "Don't move or size with cells" - this is actually irrelevant in this case
Template:
Result
Drawbacks of this solution:
- you will need to provide data for charts in different place with "Expand Range" turned on or converted from Ranges to Tables:
- you need to estimate number of rows in table at the top as you will have leave "enough" space for the table at the bottom
- you will have issues with Total line being part of data and therefore it will jump to the charts too
- In overall I would use this solution to create tables and charts in the layout required and for charts I would source data either on another hidden sheet or somewhere below

Option 2. ("expand range" turned off)

I often consider putting TOTAL at the top of the table. That simplifies process as Total line which does not interact with the charts (if the table on the left is used to produce chart on the right). It also lets you create total without having to change your data model. Assuming that you know maximum possible number of rows in the table you can make totals just like shown below. If there is less row than within SUM() range it will still produce correct value, so no issues there.

Option 3. ("expand range" turned on)

This option will allow you to make regular totals and will push down bottom table
Top right Charts with Properties option set to: "Don't move or size with cells" - this is to ensure it stays in line with the top table
Bottom chart left to be pushed down with the table: "Move but don't size with cells" option chosen.
Make rough estimate (Leave enough space) so the bottom table and bottom right charts are pushed down sufficient number of rows - (I understand that at this stage it is just estimation and it may not be accurate, and that sometimes you may have one and sometime few rows gap). This is however the simplest available option.
- A lot more advanced option which would guarantee you with perfect result would be creation of 3rd table.
- That 3rd table would be used to calculate number of rows which have to be created in excel to position bottom table and chart in right place. Obviously for this you would need to create in Qlik Sense or QlikView additional table chart which would create data set with remaining required number of rows.
- For the purpose of this I will apply in my scenario filter on top table and chart to only show values "a" and "b". That means that I need to have another object which I will position below it and which will produce 4 extra rows for where the values (c-f) would normally be. I will then make those rows invisible. See screenshots:
  - QlikView (object producing extra rows)
  - Add this object to your NPrinting template between top and bottom table:
  - And the result is:

Summary:

I tried to provide you with various different scenarios in how you can deal with it. I suggest you practice different scenarios, see what works best for you. At the end you are working with excel and we are limited to excel capabilities here. What you are trying to do can be done, but as mentioned it requires preparations and planning.

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

Ruggero_Piccoli · ‎2022-01-11

Hi,

We need some more information, like the structure of the Excel template.

Are you adding the Qlik Sense table as an image? If yes, did you try to change the Stretch option in the Properties panel to see if the image is cut?

Are you adding the Qlik Sense table as a table? If yes, did you checked the Show Totals flag in the Properties panel?

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

deep2021 · ‎2022-01-11

Hi Ruggero,

In the n printing template, in the table, we are using separate expressions for each column.

Thanks,

Deepak

Ruggero_Piccoli · ‎2022-01-11

Hi,

Ok, could you share some screenshots of the template or export the template and share the .zip (it doesn't contain any data, only the structure, you can check by opening the zip)?

I would like to check if something is missing in the template.

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

deep2021 · ‎2022-01-11

Hi Ruggero,

PFB screenshot,

We are using expressions for each and every column.

Thanks.

Ruggero_Piccoli · ‎2022-01-11

Hi,

Thanks, that screenshot lets me to understand. You dragged and dropped each single column of the Qlik Sense table into the Excel template, and that is correct but it doesn't add the totals. So with this structure of the template it is correct that totals are not visible.

To add the totals you have to follow step by step the instructions in https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ExcelReports/Make-Calculated-Column..... So create a native Excel table and use Excel formulas to add calculated totals on the bottom.

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

deep2021 · ‎2022-01-11

Hi Ruggero,

Thanks for your valuable reply. The main issue is our number of rows in the excel are not fix. Can you please suggest how to show totals in such kind of scenarios in the excel.

Thanks

Lech_Miszkiewicz · ‎2022-01-11

Hi @deep2021

Solution suggested by Ruggero works regardless of number of rows in the table. Here is also the video explaining it: https://www.youtube.com/watch?v=HjIK7cC9HJk

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.

Ruggero_Piccoli · ‎2022-01-11

Hi @deep2021 ,

Please, read carefully the linked instructions. The Excel sum() formula is entered in the template selecting at least two rows, not only one. Selecting two or more rows lets Qlik NPrinting to understand that it has to expand the formula to include all generated rows also when their number is not fix nor know from the beginning.

The DeleteRow tag is not mandatory but, when added, it indicates to Qlik NPrinting to delete that row from the final report. So you will deliver a report without an empty row between the details and the totals. If you want to keep an empty row you can avoid to add the DeleteRow tag.

Best Regards,

Ruggero

Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.

deep2021 · ‎2022-01-17

Hi Ruggero,

We have tried auto sum but in this case we have to enable the expand range option for the Top table, so in case the right hand side tale will go down.

Could you please suggest on this.

Thanks

"Total" is not visible in NPrinting chart but it is visible in Qlik Sense

Qlik NPrinting February 2021

Qlik Sense