Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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