Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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