Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
lalita_sharma
Contributor III
Contributor III

Display Null columns in Qlik Sense pivot table

 

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.

Labels (2)
1 Reply
JordyWegman
Partner - Master
Partner - Master

Hi Latita,

Did you maybe uncheck this value?

While you can see that my version works:

JordyWegman_0-1660139442691.png


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

Work smarter, not harder