topic Re: Pivot table with levels / sheet per weeks in Qlik NPrinting

Pivot table with levels / sheet per weeks

Sebastian_Dec — Thu, 12 Dec 2024 14:42:12 GMT

Hi, I have Qlik Nprinting (May 2021 SR2 Version: 21.14.5.0)

I want to make a report per week, so each sheet has a separate week number, and in there is a pivot table with data for that week.

Sheet per week I make by levles -> its ok

after:

I make a pivot table based on a normal table from Qlik -> its ok

My table is made like this, unfortunately after refreshing, it is not consistent with the expected result, I wanted the weekend number to be in B1.

How can I fix it?
before

after

Re: Pivot table with levels / sheet per weeks

David_Friend — Thu, 12 Dec 2024 15:03:46 GMT

@Sebastian_Dec as you likely already are aware, that version is well out of support.

Its best to build your pivot table in QlikSense, then import as image:

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

Re: Pivot table with levels / sheet per weeks

Lech_Miszkiewicz — Thu, 12 Dec 2024 22:46:09 GMT

Hi @Sebastian_Dec

I agree with @David_Friend that the easiest would be to use image however it is indeed terrible practice and rather poor/limiting result so to me using image is only if nothing else worked.

Now I have quite few doubts about the way you are building it:

As @David_Friend said you are on no longer supported version which in my opinion does not work correctly
you are showing that you are using levels to create sheets??? 🤔. Maybe it works maybe not - i dont know how ???, but it definitelly shouldnt be used like that. As documented in https:// help.qlik.com to create additional sheets you should be using PAGE node!!! thats it
Now regarding your question I am not understanding 1 key element - does each sheet have to contain ONLY data for that one week or do you want users who are on for example sheet 3 to have week 3 shown by default as a filter but then be able to stay on that sheet and filter to some other week?
1. If sheet needs to have only data for only that week then you should put source table as well as pivot table on the same sheet. Since that would be paging over weeks its source would be filtered hence pivot table will also be filtered to only show that one week
2. If your sheet need ability to still filter different week then the solution you are using will not work and you have 2 more options:
  1. cumbersome - prepare as many sheets as needed manually and prepopulate your straight table with number of weeks then use <deleterow> tag to remove it and set on each sheet filter you want for the pivot table. That is like "hardcoding" in NPrinting
  2. the other way would be to use macro to set filter on pivot table on each sheet - this again can have limitations.

Do not expect that you will get pivot table prefiltered based on the level or variable or formula from NPrinting - that will not work and it is actually Excel limitation.

You can PM me and I can take you through it if you have any issues.

Cheers

Lech

Re: Pivot table with levels / sheet per weeks

Sebastian_Dec — Tue, 17 Dec 2024 15:36:20 GMT

@David_Friend Thank you for your answer, we will try to update the program in the new year.

@Lech_Miszkiewicz Thanks a lot for your help, we will be testing the new solution this week, I will try to give you an answer by Friday.

Re: Pivot table with levels / sheet per weeks

Ruggero_Piccoli — Wed, 18 Dec 2024 09:18:26 GMT

Hi,

Upgrading to a supported version is strongly suggested but will not solve.

To create a different worksheet you have to use Pages, not Levels. Native Excel pivot tables works with Pages (refer to the release notes to see the minimum version that support this) but the source table must be in the same page of the pivot to be filtered by the field used to create the page.

You can hide the rows of the source table.

Native Excel pivot tables do not work inside levels.

Best Regards,

Ruggero