How show same data twice in table?

[#1]

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

[nagaiank]

Did you try using Status as the third dimension?

[#3]

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.

[jagan]

Hi,

Can you attach some sample data and qlikview file.

Regards,

Jagan.

[#5]

Good idea. I've included an attachment. So how can I make the table in it look like the second table in my question?

[bill_mtc]

Hmm please see attached file. Hope it helps.

[#7]

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:

1. Show all tasks grouped by year created (CreationDate).
2. Show cancelled tasks by year cancelled (CancelDate).

[bill_mtc · Accepted solution]

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

[jonathandienst]

Hi

See attached using a date island and count(if()) logic to show the data the way you want,

HTH

Jonathan

[#10]

Excellent, thank very much you for your swift reply! I'll remember to explain more clearly next time 🙂