# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor

## 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

0 Replies