topic Re: Pivot Table in QlikView

Pivot Table

Anonymous — Sun, 15 May 2016 09:08:19 GMT

Hi All, I have a dataset as show below.

LOAD * Inline [ DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,05/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open ];

I wanted to calculated backlog for each day and present in Pivot Table as Shown below

No of Day Month 01/01/2016 02/01/2016 03/01/2016 04/01/2016 05/01/2016

0 Day 4 1 1 1 1

1 Day 0 3 1 1 0

2 Day 0 0 2 1 1

3 Day 0 0 0 2 2

4 Day 0 0 0 0 0

How to Calculate Backlog :

Backlog = Total no of Open Defect - Total No closed Defect + Previous day Remaining Defect

Example :

On 01/01/2016

Backlog = 6 - 2 + 0 = 4

On 02/01/2016

Backlog = 3 - 3 + 4 = 4

On 03/01/216

Backlog = 2- 2 + 4 = 4

On 04/01/216

Backlog = 4- 3 + 4 = 5 So on

Expression Used for Calculating Backlog : =Count({} DefectID)> - } DefectID)>} DefectID) I have attached the QVW File.

Re: Pivot Table

ecolomer — Sun, 15 May 2016 09:11:41 GMT

Can you explain more? please

Re: Pivot Table

Anonymous — Sun, 15 May 2016 09:15:52 GMT

Hi Enrique, i have attached text file and .QVW file

Re: Pivot Table

Anonymous — Sun, 15 May 2016 10:08:16 GMT

please tell exactly

Re: Pivot Table

Anonymous — Sun, 15 May 2016 14:48:16 GMT

Hi vsudhakar,

if you see the dataset on 01/01/2016 total defect opened is 6 and defect closed is 2 so total backlog is 4. Similarly on 02/01/2016 3 new defect is opened / created + 4 defect from previous day (3 + 4) = 7 defect and 3 defect closed on same day so total 4 backlog defect 7 - 3 = 4. On 03/01/2016 2 new defect is opened and 4 from previous day so total 2 + 4 = 6 and 2 defect closed on same day. so total (2+4) - 2 = 4 Backlog defect. so on

So what i want is

No of Day Month 01/01/2016 02/01/2016 03/01/2016 04/01/2016 05/01/2016

0 Day 4 1 1 1 1

1 Day 0 3 1 1 0

2 Day 0 0 2 1 1

3 Day 0 0 0 2 2

4 Day 0 0 0 0 0

if you see the table on 01/01/2016 it has 4 backlog defect on same day so the aging is 0 day

on 02/01/2016 it has 4 backlog defect 3 which was opened on 01/01/2016 and 1 opened on 02/01/2016 So the aging of the defect is as follows 0 Day = 1 and 1 day = 3

On 03/01/2016 it has 4 Backlog defect 2 which was opened on 01/01/2016 and 1 on 02/01/2016 and 1 on 01/01/2016. So Aging of defect is as follows 0 day = 2 and 1 day = 1 and 2 day = 1 so on.

Hope it is clear now.

Re: Pivot Table

Anonymous — Mon, 16 May 2016 01:39:29 GMT

Hello Experts, Any help is appreciated.

Re: Pivot Table

Anonymous — Mon, 16 May 2016 02:13:56 GMT

Hi Enrique,, So what i want is No of Day Month 01/01/2016 02/01/2016 03/01/2016 04/01/2016 05/01/2016 0 Day 4 1 1 1 1 1 Day 0 3 1 1 0 2 Day 0 0 2 1 1 3 Day 0 0 0 2 2 4 Day 0 0 0 0 0 if you see the table on 01/01/2016 it has 4 backlog defect on same day so the aging is 0 day on 02/01/2016 it has 4 backlog defect 3 which was opened on 01/01/2016 and 1 opened on 02/01/2016 So the aging of the defect is as follows 0 Day = 1 and 1 day = 3 On 03/01/2016 it has 4 Backlog defect 2 which was opened on 01/01/2016 and 1 on 02/01/2016 and 1 on 01/01/2016. So Aging of defect is as follows 0 day = 2 and 1 day = 1 and 2 day = 1 so on. Hope it is clear now.

Re: Pivot Table

Anonymous — Mon, 16 May 2016 02:14:29 GMT

So what i want is No of Day Month 01/01/2016 02/01/2016 03/01/2016 04/01/2016 05/01/2016 0 Day 4 1 1 1 1 1 Day 0 3 1 1 0 2 Day 0 0 2 1 1 3 Day 0 0 0 2 2 4 Day 0 0 0 0 0 if you see the table on 01/01/2016 it has 4 backlog defect on same day so the aging is 0 day on 02/01/2016 it has 4 backlog defect 3 which was opened on 01/01/2016 and 1 opened on 02/01/2016 So the aging of the defect is as follows 0 Day = 1 and 1 day = 3 On 03/01/2016 it has 4 Backlog defect 2 which was opened on 01/01/2016 and 1 on 02/01/2016 and 1 on 01/01/2016. So Aging of defect is as follows 0 day = 2 and 1 day = 1 and 2 day = 1 so on. Hope it is clear now.

Re: Pivot Table

Anonymous — Mon, 16 May 2016 09:00:18 GMT

Any help?