Display Null values in Pivot Table

[lalita_sharma]

Hi

I have a pivot table in Qlik sense which looks like below:

Risk Rating	Dec-21	Jan-21	Feb-21	Mar-22	Apr-22	May-22	Total
High	0	1	0	0	1	1	3
Medium	1	2	1	1	2	2	9
Low	2	3	3	0	1	0	9
Total	3	6	4	1	4	3	21

It shows count of incidents created in last six months for selected Payment Officer.

If for a particular Payment Officer, no incidents were created in a month(in the last six month), say Feb - 21, the above table will look like below

Risk Rating	Dec-21	Jan-21	Mar-22	Apr-22	May-22	Total
High	0	1	0	1	1	3
Medium	1	2	1	2	2	8
Low	2	3	0	1	0	6
Total	3	6	1	4	3	17

It will not show the column related to Feb-21 at all. But I want to display all six months even if the data is null(see table below)

Risk Rating	Dec-21	Jan-21	Feb-21	Mar-22	Apr-22	May-22	Total
High	0	1	0	0	1	1	3
Medium	1	2	0	1	2	2	8
Low	2	3	0	0	1	0	6
Total	3	6	0	1	4	3	17

I have below expression in Creation Month to display only last six months data

=IF(Date(Monthname([Creation Date]),'MMM-YY')>=DATE(Addmonths(Date(DATE#('$(vLatestCreationMonth)','MMM YYYY'),'DD-MM-YYYY'),-5),'MMM-YY'),Date(Monthname([Creation Date]),'MMM-YY'))

Can someone please help?

Thanks in advance.

[JordyWegman]

Hi Latita,

Did you maybe uncheck this value?

While you can see that my version works:

This is script I used:

Load * Inline [
Risk, Month, Value
High, 1,4
High, 1,23
High, 3,
High, 2,13
High, 4,5
Medium, 1, 234
Medium, 2, 23
Medium, 2, 45
Medium, 3,
Medium, 5, 123
Medium, 6, 214
Low, 4, 11
Low, 4 , 234
Low, 6, 23
Low, 5, 51

];
Exit script

Jordy

Climber

[avinashelite]

This is the case of data missing scenario , you need create a master calendar with all the months in it and use the calendar month and enable the show null values

hope this solves your issue

[lalita_sharma]

Hi Jordy

Thank you for your reply

This might not work for me as in my data I have no records at all for a particular month, say Feb in above example. Hence the column for that month goes missing from the pivot table.