Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I've got a pivot table with the below structure, showing tasks per country and year created. The year is gotten with dimension "=Year(CreationDate). The count is done with expression "=Count(ID)".
Country / Year | 2011 | 2012 | 2013 |
---|---|---|---|
Sweden | 2 | 4 | 2 |
Norway | 3 | 2 | 4 |
Finland | 2 | 2 | 5 |
Some tasks have a status called "Cancelled". Now I wish to show these both under the year created AND under the year cancelled, see example below (ie. the 2 showing as Cancelled also appear under the year they were created).
Country / Year | 2011 | 2012 | 2013 | |||
---|---|---|---|---|---|---|
Status | Created | Cancelled | Created | Cancelled | Created | Cancelled |
Sweden | 2 | 4 | 2 | |||
Norway | 3 | 2 | 4 | 2 | ||
Finland | 2 | 2 | 5 |
I'm not sure how to accomplish this. I can easily do a sum of those with cancelled status, but they then show under the year created. I want them to remain there but also that they show under the year cancelled. Can anyone help with this please?
Attachment added
Hi Gustav,
Sorry for misunderstanding. Anyway, please see attached file again, or the script below:
/***********************************/
Table1:
LOAD * INLINE [
Country, CreationDate, CancelDate, ID, Status
Sweden, 2011, , 1
Sweden, 2011, , 2
Sweden, 2012, , 3
Sweden, 2012, , 4
Sweden, 2012, , 5
Sweden, 2012, , 6
Sweden, 2013, , 7
Sweden, 2013, , 8
Norway, 2011, , 9
Norway, 2011, , 10
Norway, 2011, 2013, 11, Cancelled
Norway, 2012, , 12,
Norway, 2012, 2013, 13, Cancelled
Norway, 2013, , 14
Norway, 2013, , 15
Norway, 2013, , 16
Norway, 2013, , 17
Finland, 2011, , 18
Finland, 2011, , 19
Finland, 2012, , 20
Finland, 2012, , 21
Finland, 2013, , 22
Finland, 2013, , 23
Finland, 2013, , 24
Finland, 2013, , 25
Finland, 2013, , 26
];
NoConcatenate
NewTable:
/*For Created ID*/
Load Country,
CreationDate as Year,
ID,
'Created' as Status
Resident Table1;
Concatenate(NewTable)
/*For Cancelled ID*/
Load Country,
CancelDate as Year,
ID,
'Cancelled' as Status
Resident Table1 where Status = 'Cancelled';
DROP Table Table1;
/***********************************/
Hope this helps! Thank you and good luck!
Best Regards,
Bill
Did you try using Status as the third dimension?
Yes, I did. But then the data still show up under date created as this is the first dimension I'm using. I need it to show both under date created AND date cancelled.
Thanks for your answer still.
Hi,
Can you attach some sample data and qlikview file.
Regards,
Jagan.
Good idea. I've included an attachment. So how can I make the table in it look like the second table in my question?
Hmm please see attached file. Hope it helps.
Thank you for this. Unfortunately, that shows the cancelled tasks under year created. I need to show them under the year they were cancelled.
These two things need to be shown, so that each year shows how many tasks were created and how many were cancelled:
Hi Gustav,
Sorry for misunderstanding. Anyway, please see attached file again, or the script below:
/***********************************/
Table1:
LOAD * INLINE [
Country, CreationDate, CancelDate, ID, Status
Sweden, 2011, , 1
Sweden, 2011, , 2
Sweden, 2012, , 3
Sweden, 2012, , 4
Sweden, 2012, , 5
Sweden, 2012, , 6
Sweden, 2013, , 7
Sweden, 2013, , 8
Norway, 2011, , 9
Norway, 2011, , 10
Norway, 2011, 2013, 11, Cancelled
Norway, 2012, , 12,
Norway, 2012, 2013, 13, Cancelled
Norway, 2013, , 14
Norway, 2013, , 15
Norway, 2013, , 16
Norway, 2013, , 17
Finland, 2011, , 18
Finland, 2011, , 19
Finland, 2012, , 20
Finland, 2012, , 21
Finland, 2013, , 22
Finland, 2013, , 23
Finland, 2013, , 24
Finland, 2013, , 25
Finland, 2013, , 26
];
NoConcatenate
NewTable:
/*For Created ID*/
Load Country,
CreationDate as Year,
ID,
'Created' as Status
Resident Table1;
Concatenate(NewTable)
/*For Cancelled ID*/
Load Country,
CancelDate as Year,
ID,
'Cancelled' as Status
Resident Table1 where Status = 'Cancelled';
DROP Table Table1;
/***********************************/
Hope this helps! Thank you and good luck!
Best Regards,
Bill
Hi
See attached using a date island and count(if()) logic to show the data the way you want,
HTH
Jonathan
Excellent, thank very much you for your swift reply! I'll remember to explain more clearly next time 🙂