topic Re: Pivot table formatting in Qlik NPrinting in Qlik NPrinting

Pivot table formatting in Qlik NPrinting

RK007 — Fri, 15 Nov 2024 21:48:22 GMT

Hi all,

thanks in advance.

I have a final Pivot table generated in the NPrinting, I am trying to develop the same with the NPrinting designer.

successfully able to match the data but still trying to get the formatting based on the reference, but not able to achieve it

I have tried with the conditional formatting to get the same formatting but there are differences.

i have checked the conditional formatting in the reference pivot table, but I didn't find any formatting rules.

Also,

I tried to put a filter

Can anyone help me

Regards,

Ramakoti

Re: Pivot table formatting in Qlik NPrinting

David_Friend — Tue, 21 Feb 2023 19:07:57 GMT

@RK007 what version of NPrinting, and is this coming from QlikView or QlikSense, what versions?

Have a look at this article: https://community.qlik.com/t5/Official-Support-Articles/Nprinting-reports-are-not-appearing-in-proper-format-when/ta-p/1802700

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Wed, 22 Feb 2023 00:16:40 GMT

Hi @RK007,

Yours scenario does not touch on output from Qlik (Which @David_Friend has given you hints of) as it is rather formatting of the native excel pivot table cells based on their values. And here is a problem i think.

I would still use conditional formatting to achieve what you need, however cells which are at the top as agrouping labels will only be formatted where the appropriate value is. Therefore for the range of cells in your column grouping dimension you may need to enable "Merge and center cells with labels" option because only then value ("Product 1" in my picture below ) sits on columns F:G. If you leave that "Merge and center cells with labels" option ticked off then "Product 1" is only in column "F"

Next thing to ensure would be to check option: " Refresh data when opening the file"

everything else is just excel based, so you need to think how excel will deal with cell formats when pivot table changes its content (by filtering or changing its layout by users)

cheers

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Wed, 22 Feb 2023 00:21:57 GMT

Now filters - I post this as a separate answer as it is completely different topic.

You will not be able to apply filters in the template. If your intend is to remove blanks you can still use <deleterow> tag in your template source table:

That will prevent empty row from making its way to final pivot table when report is produced.

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Wed, 22 Feb 2023 00:24:49 GMT

BY THE WAY - for the above filter solution to work you MUST build your Pivot table based on the Excel TABLE and not cells range - steps are documented in https://help.qlik.com page

Re: Pivot table formatting in Qlik NPrinting

RK007 — Wed, 22 Feb 2023 10:24:25 GMT

The source file is coming from QlikView

QlikView May 2022

Version 12.70.20000.0

NPrinting Designer May 2022 SR3

Version 22.15.11.0

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Wed, 22 Feb 2023 10:38:31 GMT

@RK007

Have you tried points i mentioned above?

Re: Pivot table formatting in Qlik NPrinting

RK007 — Wed, 22 Feb 2023 11:21:34 GMT

Hi @Lech_Miszkiewicz

thanks for you response

i have tried points as per your suggestions. but needs more clarity bcz not able to get what exactly I needed.

i am adding few more screenshots for reference

I have selected Merge cells and after that I did formatting of the client group and client subgroup and values but in the result, changes that I made in the template not replicating.

1: Appeal and other values should not be center aligned (even I selected merge cells after that I changed the text position in the template which is not replicating)

2:Same for the Client Subgroup

3: I have done the formatting for the values in the template but not appearing in the result excel

4: Same for the total values

this is the excel report, generated by the NPrinting Designer.

Also, this pivot table I have developed from the Details table (St. Table in the NPrinting), there are no extra rows to delete to avoid blanks in the client group.

Could you help where i am missing the logic..

Regards,

Ramakoti

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Wed, 22 Feb 2023 11:52:26 GMT

Hi @RK007

So can you tell me what worked?

I can say from screenshots that applying the required changes worked in terms of background colour for cells - am i right?
next problems you have are:
1. left align of values where required - is that all what is missing in terms of formats? I need to test this but it is too late for me at the moment (11:00 PM) so I will do that tomorrow.

regarding Blanks - are you putting data from Qlik Sense straight table into EXCEL TABLE? Reguired details are obviously described in help document which you should thoroughly study before starting work on NPrinitng: https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm#anchor-1

Have you followed the steps as mentioned here to create your pivot table? If you did then you would not have blanks (unless you bringing blanks from Qlik Sense data which then should be handled in Qlik Sense or filters and not NPrinting template) . I dont know what else I can tell you to help.

cheers

Re: Pivot table formatting in Qlik NPrinting

Lech_Miszkiewicz — Thu, 23 Feb 2023 09:43:08 GMT

Hi @RK007

I did little bit more testing and here are my findings:

If you select Pivot Table option "Merge & centre..." then there is no way you can left align cells
If you dont select above option you have problem with conditional formatting as it will only work on cells with values

To be honest I did not find good solution for your requirement in Excel - unfortunately excel links background colour to cells in pivot table based on their order so it is not possible to have smart dynamic colouring.

sorry