Pivot Table

[Anonymous]

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.

[ecolomer]

Can you explain more? please

[Anonymous]

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

[Anonymous]

please tell exactly

[Anonymous]

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.

[Anonymous]

Hello Experts, Any help is appreciated.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Any help?