0 Replies Latest reply: Jan 20, 2018 8:00 AM by Benjamin Buth RSS

    Calculation of preiods and visualization

    Benjamin Buth

      Hi,

       

      I searching for a solution for a calculation and visualization for the different periods walked through within the life cycle of a lead.

      The data base consists of two table (Table A,B) matched with the Lead Id. Information for later porpuses (like Businesstypes) are spread over both tables. The calculation of times should be out of table A.

      The status "New" is the status, when the lead is created and the status "Converted" and "Canceled" are the end points of the process

       

      Table A:

         

      Lead IDDate of changeChangeOld valueNew value
      A01.01.2017CreationNew
      A10.01.2017StatusNewIn Work
      A20.01.2017StatusIn WorkQualified
      A30.01.2017StatusQualifiedConverted
      B02.01.2017CreationNew
      B10.01.2017StatusNewIn Work
      B20.01.2017StatusIn WorkQualified
      B22.01.2017StatusQualifiedIn Work
      B25.01.2017StatusIn WorkQualified
      B30.01.2017StatusQualifiedConverted
      C05.01.2017CreationNew
      C09.01.2017StatusNewIn Work
      C15.01.2017StatusIn WorkQualified
      D06.01.2017CreationNew
      D07.01.2017StatusNewIn Work
      D10.01.2017StatusIn WorkConverted

         

       

      Table B:

         

      Lead IDCreation DateBusiness TypeActual Status
      A01.01.2017SparesConverted
      B02.01.2017New EQConverted
      C05.01.2017RepairQualified
      D06.01.2017SparesCanceled

       

       

      My aim is to have this result in an e.g. pivot table (other visualizations I will create later on):   

      BusinesstypeCountAvg. New in daysAvg In Work in daysAvg Qualified in daysAvg Sum of Lead time
      Spares2 (Lead A+D)+(9+1)/2=5+(10+3)/2=6,5+(10+0)/2=516,5
      New EQ1 (Lead B)8(10+3)=13(2+5)=728
      Repair1 (Lead C)130

      4

       

       

      I hope someone can help me.

       

      Thanks a lot an BR from Germany

       

      Benjamin