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

[deep2021]

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

[Lech_Miszkiewicz]

Hi @deep2021 , 

This is just setting in excel chart you are using, right?  (it is not NPrinting feature).

cheers

Lech

[deep2021]

Hi Lech,

Thanks for your valuable reply. Can you please mention the step by step process for the above solution.

Thanks

[Lech_Miszkiewicz]

Hi @deep2021 

Everything is on the screenshot I have provided:

• Right click on the chart (but not on the plot area)
  • go to Format Chart Area
  • Click on Size Icone
  • Expand Properties panel and tick the box (as on my screen shot)

cheers

[deep2021]

Hi Lech,

Thanks for your valuable feedback.

I tried with the above solution.

If we will change the properties of right hand chart to “Do not move or size with the cell” the top 2 charts will be correct , but bottom right hand side table will go down.

Please find below the image for Total.

we need to maintain 4 charts at the same time.

Thanks

[Lech_Miszkiewicz]

I understand it is a challenging scenario.

On your screenshots I see:

• 2 tables and
• 2 charts

if the issue is that the bottom table was pushed down, I guess you could assume that and remove in your template all those empty rows between table at the top and the one at the bottom... 

Is that all what needs to be on your page? Or is there something else what is not on the screenshot?

Now, I don't know what your final output needs to look like therefore I suggest we change course of this conversation and ask you to provide a sample of what you want to achieve. How does your perfect report need to look like? Also let us know what the final output format is (is it XLSX or other - PDF etc)

With the sample I can then comment what is possible and what isn't, what are the challenges and what sacrifices you will need to make. Will that work for you

cheers

[deep2021]

Hi Lech,

PFB screenshot,

The left hand table should be aligned with right hand chart.

Thanks

[Lech_Miszkiewicz · Accepted solution]

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

[Frank_S]

@deep2021

I have to say that @Lech_Miszkiewicz has provided you with extraordinary set of solutions here.

If these still do not meet your requirements perhaps you may need to consider a consulting services engagement (Qlik or a Trusted Partner) to help you further define your requirement and needed output.

Lech

Well done as always...cheers!

Kind regards...