Doubt in set analysis

[ecabanas]

Hi I have a table with salesid and customer Id.

To know the Customer Retention Rate (CRR) I'm using this formula.

Count({<[SALESID]={'PV0***'}> } SALESID)

/

Count(DISTINCT{<[SALESID]={'PV0***'}> } CUSTACCOUNT_CONTADOR)

And the monthly results are:

October  November

0,33         0,34

But If I select the two months I have a bad result, is it possible to have and average of the months? If I select the month October the result equal to 0,33, but I select october and november= Average  of 0,33 and 0,34

Many thank's

Eduard

[simenkg · Accepted solution]

This is not a problem with set analysis. You count distinct, so if CUSTACCOUNT_CONTADOR exists in both months it will still only be counted once.

try:

avg(Aggr(

Count({<[SALESID]={'PV0***'}> } SALESID)

/

Count(DISTINCT{<[SALESID]={'PV0***'}> } CUSTACCOUNT_CONTADOR)

,[MonthYear]))

[ecabanas]

MAny many Thank's Simen!!!

[ecabanas]

Hi Simen,

And what do you think about this set analysis? I have a terrible head ache!

Thank's

Eduard

(Count(distinct{<[SALESID]={'PV0***'},ESTADO_PEDIDO=,CalendarMonthName=,CalendarYear=,CONTADOR_COMPRAS-={'0','1','2'},

DATEFINANCIAL_LINE= {">=$(=max(DATEFINANCIAL_LINE-360))<=$(=max(DATEFINANCIAL_LINE))"}> } CUSTACCOUNT_CONTADOR))

/

Count(distinct{<[SALESID]={'PV0***'},ESTADO_PEDIDO=,CalendarMonthName=,CalendarYear=,

DATEFINANCIAL_LINE= {">=$(=max(DATEFINANCIAL_LINE-360))<=$(=max(DATEFINANCIAL_LINE))"}> } CUSTACCOUNT_CONTADOR)