Formatting Pivot Table in a separate sheet in Qlik Nprinting

[Sebastian_Dec]

Hi, I've seen a lot of similar topics, but none of the issues related to this: is there any way to copy the pivot table in Qlik Nprinting so that you can change the formatting in a separate sheet?

I have a pivot table which has no row (Values), there are only columns (stores = skrot_sklep) and Measures (values).
All measures are more or less complicated formulas.

This is what my table looks like in Qlik  (there are actually +100 stores and +50 rows, but I cut the rest):

This is what the code looks like in Nprinting:

And this is what excel looks like fromNprinting:
 

I know I can't edit pivot table so I came up with two ways:
a) using cell but the report is generated veeeeeery long.

b) copying the table values from Sheet1 to another sheet
And just how can I copy this table?
I tried a separate sheet with the =Sheet1!A1 reference but it only gets me the headers, the =Sheet1!$A$1 reference doesn't work either.

Nprinting:

Results:

Please help me, I don't want to use any macros or other addons, it seems to me that there must be some simple way.
Someone wrote to make a report in Nprinting that will be downloaded to Qlik, which will be created by Nprinting in the form of a regular table, but I think this is a rather complicated solution.

Version: May 2022 SR4

[Lech_Miszkiewicz · Accepted solution]

Hi Sebastian, 

Is there any reason why you are not using straight table to produce data and then excel native pivot table to apply formatting? That would be typical approach.

Use of whole <tag> for pivot table to transport data from Qlik Sense pivot table is very limiting and I would never recommend it. Since your data is quite simple this should be very easy! For simplicity I put bot tables in single sheet but standard practice would be to have straight table in other hidden sheet and the pivot of it in the sheet you want to show to others.

Let me know your thoughts

[andregas]

Hello @Sebastian_Dec ,

The @Lech_Miszkiewicz solution is the approch I prefer.

Otherwise you could try to apply a table style to your template. You should insert the pivot tag in A1 and <delete_row> in A2 and then apply a table style to area A1:A2. The table style has a lot of personalization options.
I think it could be work vertically but not horizontally. You could try to adjust the area to A1:B2 but probably you will have one column extra to your table or only the first two columns in the table area. In case you could use macro only for appling a right area to the table.

[Frank_S]

@Sebastian_Dec 

I will add this to @Lech_Miszkiewicz and @andregas point about using a straight table to build a pivot table with QlikData.

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

Suggest you do the above suggestions, rather than trying to use cell by cell reporting as that method is a 'pinpoint' method for placing specific data fields and field values (which is not dynamic and more suited to financial reports).

Kind regards...

[Sebastian_Dec · Accepted solution]

@Lech_Miszkiewicz  Thank you for the advice, but in my opinion, it's not a complete good solution, rather a halfway measure.  Creating an array doesn't give you complete selective formatting.

After much trial and bugs/error, I managed to find the right solution. All in all, I am VERY happy with my result that I will try to make a article on it in my free time.

It is possible to selectively format the pivot table in Qlik using the vertical search function, thanks to the fact that column names are the only ones that pass through the function ='Sheet1'!A1 / ='Sheet1'!B1 / ='Sheet1'!C1 /...

Solution:

1. In the worksheet, the pivot table code must be set in A1

2. In the second sheet:
- the first row must be a reference to the second sheet to the pivot table
='Sheet1'!A1 / ='Sheet1'!B1 / ='Sheet1'!C1 /...

- the second and subsequent rows are the vertical search function (VLOOKUP() - sorry, but i have polish version of excel)

3. After loading we have our data

And most importantly, we can do selective formatting:
Before

After