Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Read about the latest Qlik Community enhancements on the Community News blog!
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Recursive set analysis


I have a table with raw sales data like this,



Month ID (YYMM)



Invoice NO.

Sales Amount

Sales Qty

I need to calculate the retention rate of customer (in last 6 months) with definition like this,

Say current Month ID is 201606

Anyone who purchase between 201506 to 201512 with repeated purchase between 201601 to 201606.

I can use variable to calculate the start and end month of both period and use this formula to get the retention,

count(DISTINCT {<[Month ID]={'>=$(vRetentionXStart)<=$(vRetentionXEnd)'},

  VIP_MAIN_NO=p({<[Month ID]={'>=$(vRetentionYStart)<=$(vRetentionYEnd)'}>}Customer_ID)>}Customer_ID) /

  count(DISTINCT {<[Month ID]={'>=$(vRetentionYStart)<=$(vRetentionYEnd)'}


Question is,

I want to build a pivot table which can dynamically show the individual retention rate of each month for the last 12 months. Like this,

Brand     201505     201506     201507.........     201606

A               XX               XX          XX                        XX

B               XX               XX          XX                        XX


Any help is appreciated.

0 Replies