Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
benjamin_buth
Contributor
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 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

0 Replies