bar graph, with relation acumulative 3 month and acumulative 12 months, for customer retention

[Anonymous]

I need to make a bar graph, per month than the number I add different customers who bought in the last 3 months, and divide by the number of customers who bought in the last 12, is to make a graph of customer retention The formula I use is

rangesum(count(distinct(codClientes)),0,3)

/

rangesum(count(distinct(codClientes)),0,12)

The problem is that if a customer buys repeated in the period that I see tells me twice, and I just want to show it once, someone would aid in doing this.

Thank you very much

[pover]

Pablo,

This is a problem somebody posted a couple months ago and the conclusion was to use a date island table or set analysis because there is not a known way to do a rangesum(distinct ...) or a rangecount(distinct ... ). The set analysis would be the preferred way, but you have to make an expression for every month instead of the month being the dimension. The expression would be something like this:

count({$<Year=,Month=,MonthYear={">=$(=addmonths(max(MonthYear),-3))<=$(=max(MonthYear))"}>} distinct codClientes)

/

count({$<Year=,Month=,MonthYear={">=$(=addmonths(max(MonthYear),-12))<=$(=max(MonthYear))"}>} distinct codClientes)

Instead of a dynamic MonthYear, you could do one for each month, too.

The date island table will allow you still use a Month_Island as a dimension, but the count(distinct if(Month>=addmonths(Month_Island,-3), codClientes)) can be very slow and should be avoided if possible.

Regards.

Regards.

[Anonymous]

Karl, do you have any example for this?

regards

[pover]

Pablo, Here's an example with the set analysis for the first 4 months of the year you select.

Regards.

[Anonymous]

thanks Karl, im going try whit this example.