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

# Calculation of preiods and visualization

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 ID Date of change Change Old value New value A 01.01.2017 Creation New A 10.01.2017 Status New In Work A 20.01.2017 Status In Work Qualified A 30.01.2017 Status Qualified Converted B 02.01.2017 Creation New B 10.01.2017 Status New In Work B 20.01.2017 Status In Work Qualified B 22.01.2017 Status Qualified In Work B 25.01.2017 Status In Work Qualified B 30.01.2017 Status Qualified Converted C 05.01.2017 Creation New C 09.01.2017 Status New In Work C 15.01.2017 Status In Work Qualified D 06.01.2017 Creation New D 07.01.2017 Status New In Work D 10.01.2017 Status In Work Converted

Table B:

 Lead ID Creation Date Business Type Actual Status A 01.01.2017 Spares Converted B 02.01.2017 New EQ Converted C 05.01.2017 Repair Qualified D 06.01.2017 Spares Canceled

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

 Businesstype Count Avg. New in days Avg In Work in days Avg Qualified in days Avg Sum of Lead time Spares 2 (Lead A+D) +(9+1)/2=5 +(10+3)/2=6,5 +(10+0)/2=5 16,5 New EQ 1 (Lead B) 8 (10+3)=13 (2+5)=7 28 Repair 1 (Lead C) 1 3 0 4

I hope someone can help me.

Thanks a lot an BR from Germany

Benjamin