Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ringolau
Contributor III
Contributor III

Recursive set analysis

Hi,

I have a table with raw sales data like this,

Sales

---------

Month ID (YYMM)

Customer_ID

Brand

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)'}

  >}Customer_ID)

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

C

Any help is appreciated.

0 Replies