Recursive set analysis

ringolau — Fri, 13 May 2016 07:43:10 GMT

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

Any help is appreciated.

topic Recursive set analysis in QlikView