topic Re: Nprinting pivot group by or formatting in excel in Qlik NPrinting

Nprinting pivot group by or formatting in excel

qliklizzy — Sat, 16 Nov 2024 03:31:17 GMT

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'))

Re: Nprinting pivot group by or formatting in excel

Ruggero_Piccoli — Tue, 21 May 2019 10:36:11 GMT

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:

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

Best Regards,

Ruggero

Re: Nprinting pivot group by or formatting in excel

qliklizzy — Tue, 21 May 2019 11:45:32 GMT

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