7 Replies Latest reply: Feb 24, 2017 5:04 AM by Ganesh S RSS

    Two dates to link to common Year & Month

    nayan lalla

      Hi

       

      I have call log data and for each call there is a  "created date" and a "closed date".

      In my analysis I want to show how many calls were created or closed in a month or year. 

      I can create a year and month for fields for each of the dates, however the issue is I don't want a two years and two months.

      How do I create one  year and month field for both dates.

       

      Please can you assist.

       

      Below is a sample of the data.

       

      Kind regards

      Nayan

       

          

      Created DateClose DateCall ID
      31-Aug-1620-Sep-16SR76
      01-Sep-1601-Sep-16SR128
      01-Sep-1602-Sep-16SR376
      01-Sep-1620-Sep-16SR339
      02-Sep-1605-Sep-16SR546
      05-Sep-1606-Sep-16SR761
      05-Sep-1606-Sep-16SR700
      06-Sep-1612-Sep-16SR1180
      06-Sep-1615-Sep-16SR1177
      06-Sep-1604-Nov-16SR1215
      07-Sep-1608-Sep-16SR1321
      07-Sep-1609-Sep-16SR1400
      07-Sep-1613-Sep-16SR1299
      12-Sep-1614-Sep-16SR2097
      12-Sep-1614-Sep-16SR2254
      12-Sep-1606-Oct-16SR2279
      12-Sep-1620-Oct-16SR2189
      12-Sep-1615-Dec-16SR2105
      12-Sep-1623-Dec-16SR2131
      13-Sep-1620-Oct-16SR2547
      14-Sep-1616-Sep-16SR2567
      14-Sep-1624-Oct-16SR2661
      15-Sep-1619-Sep-16SR3262
      15-Sep-1630-Sep-16SR3217
      15-Sep-1619-Oct-16SR3124
      16-Sep-1620-Sep-16SR3529
      16-Sep-1626-Oct-16SR3329
      19-Sep-1621-Sep-16SR3951
      19-Sep-1623-Sep-16SR4140
      19-Sep-1610-Oct-16SR3785
      19-Sep-1612-Oct-16SR4148
      19-Sep-1625-Oct-16SR3745
      21-Sep-1622-Sep-16SR4835
      21-Sep-1625-Oct-16SR4656
      22-Sep-1619-Oct-16SR5161
      23-Sep-1626-Sep-16SR5256
      26-Sep-1627-Sep-16SR5477
      27-Sep-1628-Sep-16SR5950
      27-Sep-1629-Sep-16SR5855
      27-Sep-1611-Oct-16SR5912
      27-Sep-1620-Oct-16SR5754
      30-Sep-1604-Oct-16SR6999
      03-Oct-1603-Oct-16SR7060
      03-Oct-1607-Oct-16SR7305
      04-Oct-1623-Dec-16SR7334
      04-Oct-1623-Dec-16SR7330
      06-Oct-1613-Oct-16SR8072
      06-Oct-1627-Oct-16SR8159
      10-Oct-1619-Oct-16SR8621
      11-Oct-1607-Nov-16SR9030
      11-Oct-1607-Nov-16SR9039
      12-Oct-1614-Oct-16SR9184
      12-Oct-1610-Nov-16SR9386
      14-Oct-1617-Oct-16SR9690
      14-Oct-1621-Oct-16SR9841
      17-Oct-1618-Oct-16SR9941
      17-Oct-1618-Nov-16SR10116
      18-Oct-1610-Nov-16SR10487
      18-Oct-1612-Dec-16SR10364
      18-Oct-1605-Jan-17SR10378
      24-Oct-1602-Nov-16SR11424
      25-Oct-1628-Oct-16SR11509
      25-Oct-1618-Nov-16SR11513
      26-Oct-1610-Nov-16SR11875
      26-Oct-1619-Dec-16SR11713
      31-Oct-1631-Oct-16SR12431
      31-Oct-1617-Nov-16SR12630
      01-Nov-1601-Nov-16SR12697
      02-Nov-1603-Nov-16SR13054
      03-Nov-1611-Nov-16SR13090
      04-Nov-1607-Nov-16SR13345
      07-Nov-1608-Nov-16SR13612
      07-Nov-1608-Nov-16SR13768
      07-Nov-1609-Nov-16SR13722
      07-Nov-1610-Nov-16SR13622
      07-Nov-1617-Jan-17SR13830
      08-Nov-1609-Nov-16SR14010
      09-Nov-1611-Nov-16SR14212
      09-Nov-1618-Nov-16SR14246
      10-Nov-1611-Nov-16SR14427
      10-Nov-1614-Nov-16SR14369
      14-Nov-1615-Nov-16SR14901
      15-Nov-1616-Nov-16SR15073
      16-Nov-1622-Nov-16SR15246
      17-Nov-1618-Nov-16SR15503
      18-Nov-1630-Nov-16SR15920
      18-Nov-1623-Dec-16SR15822
      21-Nov-1621-Nov-16SR16064
      21-Nov-1601-Dec-16SR16166
      22-Nov-1624-Nov-16SR16543
      23-Nov-1623-Nov-16SR16565
      23-Nov-1625-Nov-16SR16596
      24-Nov-1628-Nov-16SR16806
      25-Nov-1620-Dec-16SR17182
      25-Nov-1623-Dec-16SR17216
      28-Nov-1605-Dec-16SR17413
      29-Nov-1605-Dec-16SR17760
      29-Nov-1629-Dec-16SR17576
      30-Nov-1620-Dec-16SR17857
      30-Nov-1620-Dec-16SR17998
      30-Nov-1620-Dec-16SR18044
      01-Dec-1602-Dec-16SR18097
      01-Dec-1620-Dec-16SR18158
      01-Dec-1620-Dec-16SR18154
      01-Dec-1620-Dec-16SR18183
      02-Dec-1605-Dec-16SR18372
      02-Dec-1605-Dec-16SR18386
      05-Dec-1605-Dec-16SR18562
      05-Dec-1605-Dec-16SR18542
      05-Dec-1606-Dec-16SR18612
      05-Dec-1608-Dec-16SR18446
      05-Dec-1613-Dec-16SR18505
      06-Dec-1609-Dec-16SR18865
      07-Dec-1607-Dec-16SR19135
      07-Dec-1621-Dec-16SR19383
      08-Dec-1608-Dec-16SR19512
      09-Dec-1609-Dec-16SR19693
      12-Dec-1614-Dec-16SR20082
      14-Dec-1603-Jan-17SR20432
      14-Dec-1612-Jan-17SR20482
      19-Dec-1620-Dec-16SR20918
      20-Dec-1622-Dec-16SR21086
      21-Dec-1623-Dec-16SR21329
      23-Dec-1617-Jan-17SR21571
      30-Dec-1603-Jan-17SR21738
      03-Jan-1703-Jan-17SR21754
      03-Jan-1703-Jan-17SR21807
      03-Jan-1703-Jan-17SR21805
      03-Jan-1703-Jan-17SR21816
      03-Jan-1703-Jan-17SR21837
      03-Jan-1703-Jan-17SR21825
      03-Jan-1704-Jan-17SR21891
      04-Jan-1706-Jan-17SR22041
      06-Jan-1709-Jan-17SR22349
      09-Jan-1710-Jan-17SR22888
      09-Jan-1711-Jan-17SR22889
      10-Jan-1723-Jan-17SR23064
      11-Jan-1713-Jan-17SR23415
      12-Jan-1718-Jan-17SR23794
      15-Jan-1716-Jan-17SR24012
      16-Jan-1719-Jan-17SR24202
      17-Jan-1718-Jan-17SR24544
      18-Jan-1720-Jan-17SR24829
      23-Jan-1724-Jan-17SR25651
        • Re: Two dates to link to common Year & Month
          Deepak Sharma

          how are you planing to deal with below scenario?

           

          18-Oct-1605-Jan-17SR10378
            • Re: Two dates to link to common Year & Month
              nayan lalla

              Hi Deepak

               

              Thank you for responding.  Yes, this is the exact issue I'm having.  Where the month/year of the created call is different to the month/year of the closed call

               

              So for this Call ID, count of "created" calls will be Oct will be 1 and count of closed calls in Jan will be 1.  So if I select Oct, it should show 1 created call in Oct and Jan is grey'd out..  And if I select Jan, it shows 1 closed call in Jan, and Oct is grey'd out.

               

              Hope 'I'm making sense.

               

              Kind regards

              Nayan

            • Re: Two dates to link to common Year & Month
              Ganesh S

              Can you elaborate on this following statement?

               

              I can create a year and month for fields for each of the dates, however the issue is I don't want a two years and two months.

              How do I create one  year and month field for both dates.

               

               

               

              -Ganesh

                • Re: Two dates to link to common Year & Month
                  nayan lalla

                  Hi Ganesh

                   

                  I have made a pivot of the data (summary and detail).  See below.

                  So let say I select 2016 and Sep, the value that must show is 41 calls created and 25 calls closed.

                   

                  Hop this helps

                   

                  Kind regards

                  Nayan

                   

                  Summary:    

                   

                  Created
                  AugSepOctNovDecJanGrand Total
                  2016141253424 125
                  2017 1919
                  Grand Total14125342419144
                  Closed
                  Row LabelsAugSepOctNovDecJanGrand Total
                  2016 25253336 119
                  2017 2525
                  Grand Total 2525333625144

                   

                  Detail:

                   

                        

                  CreatedSep        ClosedSep
                  Call IDCreated DateClose DateTotal     Call IDClose DateCreated DateTotal
                  SR11772016/09/062016/09/151     SR11772016/09/152016/09/061
                  SR11802016/09/062016/09/121     SR11802016/09/122016/09/061
                  SR12152016/09/062016/11/041     SR1282016/09/012016/09/011
                  SR1282016/09/012016/09/011     SR12992016/09/132016/09/071
                  SR12992016/09/072016/09/131     SR13212016/09/082016/09/071
                  SR13212016/09/072016/09/081     SR14002016/09/092016/09/071
                  SR14002016/09/072016/09/091     SR20972016/09/142016/09/121
                  SR20972016/09/122016/09/141     SR22542016/09/142016/09/121
                  SR21052016/09/122016/12/151     SR25672016/09/162016/09/141
                  SR21312016/09/122016/12/231     SR32172016/09/302016/09/151
                  SR21892016/09/122016/10/201     SR32622016/09/192016/09/151
                  SR22542016/09/122016/09/141     SR3392016/09/202016/09/011
                  SR22792016/09/122016/10/061     SR35292016/09/202016/09/161
                  SR25472016/09/132016/10/201     SR3762016/09/022016/09/011
                  SR25672016/09/142016/09/161     SR39512016/09/212016/09/191
                  SR26612016/09/142016/10/241     SR41402016/09/232016/09/191
                  SR31242016/09/152016/10/191     SR48352016/09/222016/09/211
                  SR32172016/09/152016/09/301     SR52562016/09/262016/09/231
                  SR32622016/09/152016/09/191     SR5462016/09/052016/09/021
                  SR33292016/09/162016/10/261     SR54772016/09/272016/09/261
                  SR3392016/09/012016/09/201     SR58552016/09/292016/09/271
                  SR35292016/09/162016/09/201     SR59502016/09/282016/09/271
                  SR37452016/09/192016/10/251     SR7002016/09/062016/09/051
                  SR3762016/09/012016/09/021     SR762016/09/202016/08/311
                  SR37852016/09/192016/10/101     SR7612016/09/062016/09/051
                  SR39512016/09/192016/09/211     Grand Total    25
                  SR41402016/09/192016/09/231    
                  SR41482016/09/192016/10/121    
                  SR46562016/09/212016/10/251    
                  SR48352016/09/212016/09/221    
                  SR51612016/09/222016/10/191    
                  SR52562016/09/232016/09/261    
                  SR5462016/09/022016/09/051    
                  SR54772016/09/262016/09/271    
                  SR57542016/09/272016/10/201    
                  SR58552016/09/272016/09/291    
                  SR59122016/09/272016/10/111    
                  SR59502016/09/272016/09/281    
                  SR69992016/09/302016/10/041    
                  SR7002016/09/052016/09/061    
                  SR7612016/09/052016/09/061    
                  Grand Total    41