Nprinting pivot group by or formatting in excel

[qliklizzy]

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

[Ruggero_Piccoli]

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:

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

Best Regards,

Ruggero

[qliklizzy]

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