topic Re: Rank and P() in QlikView

Rank and P()

Wed, 05 Nov 2014 07:34:01 GMT

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?

Re: Rank and P()

Gysbert_Wassenaar — Wed, 05 Nov 2014 10:49:54 GMT

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

Re: Rank and P()

Wed, 05 Nov 2014 13:50:54 GMT

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

Re: Rank and P()

Gysbert_Wassenaar — Wed, 05 Nov 2014 14:01:43 GMT

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