Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Sebastian_Dec
Creator II
Creator II

Pivot table with levels / sheet per weeks

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

Sebastian_Dec_4-1734014202641.png

 

Sebastian_Dec_0-1734013879818.png

after:

Sebastian_Dec_1-1734013910195.png

 

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

Sebastian_Dec_2-1734013981116.png

 

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

Sebastian_Dec_5-1734014241694.png

after

Sebastian_Dec_6-1734014475386.png

 

 

 

Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.
Labels (2)
4 Replies
David_Friend
Support
Support

@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....

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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:

  1. As @David_Friend said you are on no longer supported version which in my opinion does not work correctly
  2. 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
  3. 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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Sebastian_Dec
Creator II
Creator II
Author

@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.

Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.
Ruggero_Piccoli
Support
Support

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.

Ruggero_Piccoli_0-1734513434157.png

You can hide the rows of the source table. 

Native Excel pivot tables do not work inside levels. 

Best Regards,

Ruggero

 



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.