Qlik NPrinting - filtered substotals

[daniele_flori_maggioli]

Hello,

i've a problem while creating an NPrinting report using excel template.

Having those sample data:

i need to create a PDF report containing a Pivot table with subtotal for field 'totale carico aggredito' filtered base on 'indicatore_procedura' = 'PRIMO'. But i can't find a way to obtain what i want. The only solution that i found is to add another column 'Aggredito Procedura' (as you can see on the screenshot) with value 0 only for certain rows...and on the report i have this result:

As you can see is something much different from what i obtained for the same report on Sense:

Another solution that i found is: create a new row for any record with 'indicatore_procedura' <> 'PRIMO' with 'totale carico aggredito' multiplied for -1 and use this new record to bring down the subtotal excluding this only for the visualization but not for the subtotal.

The problem here is that to obtain the ability to use the option 'include filtered element on total' in excel you need to use the data model to create the pivot that seems to be not compatible with NPrinting.

Is it possible, in this case, obtain the same report created on sense also on NPrinting? if yes, how?

Thanks in advance

[David_Friend]

@daniele_flori_maggioli first off, note that version of NPrinting is no longer supported and must be updated.

Qlik Sense pivot tables can only be added as images in Qlik NPrinting.

See: https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm

Note that this is documentation for May 2021, the documentation for your version has been archived is not available.

[daniele_flori_maggioli]

Hi David, 

thanks for your answer. For what concern the version i've alredy requested the upgrade of the application to who is in charge of the server.

I'm not adding the sense pivot as image on the report because we know that could be some issues on pagination with that, instead it we usually import the data from qlik as plain table and after that we create a pivot on the excel template as described in the tutorial you linked...but actually i can't find a way to filter only the subtotal on the excel pivot table...

The only workaround that i found to obtain the exact same pivot table that i have on Sense is to use Excel Data Model but i read on this topic that is not supported on any version of nprinting

[andregas · Accepted solution]

Hi Daniele,

You could do it using Excel straight table, if Excel Pivot table isn't a limit.

You should creating a dedicated table for all dimension upper the last one (a table with only one dimension), according to your screenshoot, there will be:

• a first table with dimension tipo_procedura and all measures
• a second table, your Sense table, but you should use straight table object

You should use the first table as level and the second as table tag.
In Excel you could insert it as the following example:

P.S. I suggested you to create a new table with only the first dimension to use it as level because if you use directly the field as level you could have same row that not exist on the table in case because the measures have 0 as value so Sense hides them. Using a field as level export all value of the field in the data model, instead an object used as level export only the visible row on the Sense object. Pay attention because it's request more time to export.

Let me know if it helps in your case or you must use the Excel Pivot Table.

[daniele_flori_maggioli]

Hi Andre,

as you suggested i abbandoned the pivot table and used levels to create a structure similar to the pivot.

This is the solution i adopted in case some one could need it:

Assuming that column 'B' is valorized with 'indicatore_procedura' and is hidden.

the subtotal of 'carico aggredito' is calculated with this formula: =SUMIF(D12:D15;VLOOKUP("PRIMO";B12:F15;3;FALSE))

the subtotal of 'incassato su carico aggredito' is calculated with this formula: =SUM(E11:E15)

the subtotal of '% incassato su carico aggredito' is calculated with this formula: =SUMIF(F12:F15;VLOOKUP("PRIMO";B12:F15;5;FALSE))

This is the result:

thx very much for yours help!