Calculation of preiods and visualization

[Anonymous]

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