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.
