Rank and P()

[#1]

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?

[Gysbert_Wassenaar]

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])

[#3]

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])

[Gysbert_Wassenaar]

Yes, probably. The first expression you posted is incomplete.