Calculted value from a pivot table Nprinting

[ra162512]

Hello,

I have the following table

Thanks to this table I did a pivot table:

The last row  " Average week" is calculted by  :  "Total général"/count(number of rows) 

Now I would like to do the same in NPrinting.

When I get my table from Qlik I get this:

 

Then when I try to do a pivot table I get this:

But I don't see how to calculate Average week from the pivot table. 

Is it possible to achieve this in Nprinting?

I'm using Nprinting 18.

Thanks!

[Ruggero_Piccoli · Accepted solution]

Hi,

You can insert the Excel function GETPIVOTDATA() in the Qlik NPrinting Excel template. It is documented in the official Excel help site: https://support.office.com/en-gb/article/GETPIVOTDATA-function-8C083B99-A922-4CA0-AF5E-3AF55960761F

Pay attention, if you insert the function under the template of the pivot when it is produced the function could be overlapped. So you have to estimate the number of rows to skip or place the formula in another place, for example in the side of the pivot.

Best Regards,

Ruggero

[Lech_Miszkiewicz · Accepted solution]

Hi, 

I will try to turn your head in slightly different direction when thinking about this solution. You have taken approach that this one row has to be derived rom excel pivot table. 

If I would build this report I would calculate those values in Qlik object and return them directly from Qlik.

[ra162512]

Ok thank you! I wasn't sure if I had to do it in Qlik or in Nprinting !

Now I think the best pratice is to do it in Qlik Sense even if it can take more time.

Thanks

[QFanatic]

Hello, Thank you for your reply.

To explain a bit more..I am basing the Pivot on an underlying straight table. One of the fields there is PERIOD.

So the users need to be able to filter on that PERIOD field, so I've dragged that to the "Filters" in Excel.

1. if I understand correctly, in my current scenario, the data in the Pivot will update accordingly as user selects Period in the filter box.

2. I need to take this one step further. I need to filter on the field, as above, but also another field date field (Flag_date) must NOT be equal to the field in (1) above. its a complicated scenario. so I have to be able to "grab" that field value of Period, so in my GETPIVOT statement I can say where A-Field <> the selected PERIOD filter value. Hope that makes sense.

I cannot share a copy of the template - its company related.

thanks

[Ruggero_Piccoli]

Hi,

If you create a native Excel pivot by follow the instructions in https://help.qlik.com/en-US/nprinting/November2019/Content/NPrinting/ExcelReports/Pivot-table-Excel.... (avoid to use the Levels based procedure because it is slower), Qlik NPrinting will create Excel reports with data from QlikView or Qlik Sense at the moment of creation. The steps in the chapter "Set data to be refreshed" will assure that the data in the pivot are updated with the data in the table on report opening. Data in reports are not linked with the sources, so when you interact with then you are working with the copy in the Excel report.

Qlik NPrinting produces normal Excel so you can use Excel features. About point 2 I suggest you to create the formula that you need in an Excel file outside Qlik NPrinting and only when you are sure it works copy it into the template.

I don't know if it is possible in your scenario, but another solution could be to create a field for filtering in the source Qlik Sense app or QlikView documents. This could help you in simplifying the filtering formula.

Best Regards,

Ruggero