In NPrinting Designer calculated columns don't show up in pivot table

[Sapphire]

Hello all!

I have a small question that´s driving me crazy, regarding the use of pivot tables in the NPrinting Designer.

The situation:

I want to create a pivot table using the Excel-Features provided by NPrinting, that origins from the Data of a normal table which is loaded in by NPrinting directly from Qlik. This normal table regarding the "raw data" contains duplicates which I aim to correct using a simple countif-expression. My aim is to create a pivot showing the amount of distinct serial numbers per location. This pivot table is the primary goal of my task.

The problem with example:

So basically, I have, lets say, the following data table as foundation for the pivot-to-be:

Data Table as shown in Designer:

Serialnumber	Location	Calculated Field
<Serialnumber>	<Location>	=1/countif([Serialnumber];[@Serialnumber])
<deleterow>

Data Table (when loaded - for visualisation):

Serialnumber	Location	Calculated Field
111	Here	1
112	There	0.33333
112	There	0.33333
112	There	0.33333
123	SomewhereElse	1

My issue is now: If I try to set up a pivot table directly in the designer by marking the whole table and klicking on "create pivot table",  it will create a pivot table, showing me only the Serialnumber and Location as dimension, but not the calculated field which I need.

However, if I let the document get created and then create a pivot table in Excel, this is no problem.

The question:

How can I let the calculated column get integrated in the pivot table, so that I am able to correct the values in the pivot table?

I hope you can help me with that problem!

Faithful regards,

Sapphire

[Frank_S · Accepted solution]

Hi @Sapphire 

I would recommend that rather than using an MS excel calculated field that you ask the Qlik View/Qlik Sense developer to build the calculated column in Qlik Sense straight table (not a pivot table).
When done, reload the NP connection then the column will be available as expected by NPrinting.

Then use this article to complete the pivot table with Qlik Data.

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

It might be possible to do it as you asked but the above is the supported method. (others here might have ideas for you as well).

Kind regards...