Pivot Table formatting

[greend21]

I have a Qlik Sense pivot table created that has counts and amounts formatted as numbers and money. In the excel template editor whenever I hit preview it does not apply these formats. Any idea how I can add them? Since these are pivot tables I cannot drag each field individually into the editor and having Keep Source Formats selected has not worked. Also, is it possible to apply heading colors and such?

It looks like there should be a format editor available once you turn off Keep Source Formats but I do not see that.

"The following sections list the effects that the elements have that are used to compose the formatting code. This code is entered into the Format field that appears after deactivating Keep Source Formats." ( From https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ReportsDevelopment/Which-Formats-C...)

[Frank_S · Accepted solution]

Unfortunately it's not possible to manage field level formatting with a Pivot Table since the entire chart is exported from Qlik Sense (or QlikView) as is rather than the individual fields within the pivot table.

To work around this, I suggest:

• Create a copy of your pivot table and convert the copy to a straight table.
• Save your sense (or QVW) app, reload the NPrinting meta data so that the new chart is available to NPrinting
• Use the straight table chart now visible to NPrinting to create a Pivot Table with QS (or QV) data by following the instructions one of the links below:

https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ExcelReports/Excel-pivot-table-rep...

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

This approach is common when it is necessary to have a greater level of control of field level formatting as a report developer. This when permissions to format the source pivot table in Qlik Sense or QlikView are limiited or simply require  a separate pivot view that is different to the QV or QS chart without changing the source chart.

Hope this helps.

[greend21]

Okay, thanks. In theory, keeping the source formats should display the numbers correctly though right? It's strange because on my other pivot table the numbers are correctly formatted in NP but they are both formatted the same way in the QS app.

[Frank_S]

No worries @greend21 

Please check this link regarding some Qlik Sense Export limitations with respect to Pivot Tables.

https://help.qlik.com/en-US/nprinting/February2019/Content/NPrinting/ReportsDevelopment/Qlik-objects...

It's likely that you are hitting one of the limitations of the export functionality with Qlik Sense pivot tables and their usage with NPrinting.

In the mean time, I suggest using the image export (shown above) if you need the color and other formatting to appear as expected.

The issue is actually with how Qlik Sense exports data. NPrinting leverages this data export and at the moment, it doesn't appear to export background and other items.

Sorry for the inconvenience but hopefully this sheds light on what you are experiencing.

[greend21]

When you say to make a copy of the pivot and convert it to a straight table, you would make this a master item right? I have my straight table copy saved as a master item, but I cannot see it in NPrinting even after refreshing the meta data. I do not have it visible in my application since it would be pointless to have the pivot and straight tables showing the same information.

[Frank_S]

Actually I wasn't suggesting usage as a master item... Master items cannot be used with NPrinting since they do not have object IDs behind them. See https://support.qlik.com/articles/000042515

I mean specifically is to right click/copy the Pivot Table in your sense while in 'Edit' mode then pasting it back into the sheet (resulting another Pivot Table object with a separate object ID).

Then drag a 'Table' chart object over the copied Pivot Table

Select 'Convert To Table'

Click 'Done' 

Refresh your NPrinting meta data connection and you should see the new straight 'table' chart in your available chart list in the NPrinting designer.

[greend21]

In our situation I don't think this will work then. Our MIS team has us develop in NPrinting that is connected to a UAT environment. I thought I could use a master item from the environment in the report. In this case, I'd have to have an extra sheet that has my two pivot tables converted to straight tables solely for the purpose of NPrinting. Maybe if our admins can hide it.

[edwards142]

Honestly, I don’t have that much of knowledge regarding Access pivot table. When I worked with MS Access pivot table  for the first time.  I have visited so many tutorial sites and finally got help from one informative post. Check this out, it will surely help you too.

http://www.accessrepairnrecovery.com/blog/complete-information-about-ms-access-pivot-table