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: 
qliklizzy
Creator II
Creator II

Nprinting pivot group by or formatting in excel

Hi

I have brought a straight table in following instructions from another post which worked well, I have formatted it to suit my reporting needs.  I needed to pivot the data and show a count by discharge date but by year/month where I would usually just do a group by on the discharge date (date time field) - this couldn't be done - so I created a year and month field in the app, just by using  the below :  but when I pivot year into the row or column labels it just reverts back to number - I have unchecked "keep source formats" and have tried formatting cells  by sheer messing around it has formatted the year but not the month at all - is that because its the 2nd row label. - I am pulling my hair out over something so easy, usually 😐 

any help you could offer would be amazing! thanks

=(Date(MonthStart([Discharge Date]),'yyyy'))

 

 

 

Labels (2)
2 Replies
Ruggero_Piccoli
Support
Support

Hi

If you followed https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm you have an Excel table in your template so you could add Excel calculated columns to obtain the year and month like I did in:

Calculate Year Month.png

Then you will use the two calculated column in the pivot.

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.
qliklizzy
Creator II
Creator II
Author

Thanks !

this works very well managing to group it by month and year

The month is coming through as number though, i.e. Jan =1 as the text function to format it as MMM doesn't work making it all text as Jan; its so difficult to know what to format in the pivot as you cannot see much without checking out preview.

 

thanks for your help