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
