Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to calculate the retention rate of the top 30% spending customer. As the 30% is base on a dynamic selection of the date period so I have to use the rank function and can't calculate it during the data load.
This is how I can calculate the retention rate, a customer is retained if he purchase in 13 to 24 period and repurchase in 0 to 12 period. This is what I can do,
=count(DISTINCT
{<[Month ID]={'>=$(vRetentionXEnd)<=$(vRetentionXStart)'}, [Invoice VIP Main No]=p({<[Month ID]={'>=$(vRetentionYEnd)<=$(vRetentionYStart)'}>}[Invoice VIP Main No])
I can get the top 30% VIP customer base on this Rank function,
=Count(DISTINCT [Invoice VIP Main No]={=rank(sum([Invoice Net Amount in HKD]),[Invoice VIP Main No])<=$(vFormularDistinctVIP)*0.3'}>}[Invoice VIP Main No]>}[Invoice VIP Main No])
The point is how can I put these two conditions together?
You can try using the * intersect operator for set analysis expressions:
=count(DISTINCT
{< [Month ID]={'>=$(vRetentionXEnd)<=$(vRetentionXStart)'},
[Invoice VIP Main No]=p({<[Month ID]={'>=$(vRetentionYEnd)<=$(vRetentionYStart)'} >[Invoice VIP Main No]) >
*
< [Invoice VIP Main No]={=rank(sum([Invoice Net Amount in HKD]),
[Invoice VIP Main No])<=$(vFormularDistinctVIP)*0.3'}>}[Invoice VIP Main No] >}
[Invoice VIP Main No])
Thanks for your tips, I think it is close as there is a syntax error. Will it be possible that p() function is missing parameter to return the result set of VIP no.?
=count(DISTINCT
{< [Month ID]={'>=$(vRetentionXEnd)<=$(vRetentionXStart)'},
[Invoice VIP Main No]=p({<[Month ID]={'>=$(vRetentionYEnd)<=$(vRetentionYStart)'} >
*
< [Invoice VIP Main No]={=rank(sum([Invoice Net Amount in HKD]),
[Invoice VIP Main No])<=$(vFormularDistinctVIP)*0.3'}>}[Invoice VIP Main No] >}
[Invoice VIP Main No])
Yes, probably. The first expression you posted is incomplete.