Avg(Aggr(count(DISTINCT field),dimension)) outputs strange result

[#1]

Hello,

I am building a tool to analyze POS data and I'd like to evaluate the fidelity of the customers at different service counters: the higher the ratio [number of purchasing acts by client X on this counter]/[total number of purchasing acts by client X whatever the counter], the higher the fidelity of this customer to this counter. Then I want to take the average of these "fidelity indexes" for all the customers to get the counter fidelity index.

This gives, in the context of a chart with service_point_name as dimension:

Avg(Aggr(count(DISTINCT transaction_uid),customer_uid))/avg(total Aggr(count(DISTINCT transaction_uid),customer_uid))

Or at least I thought so, but it doesn't work. I have ratios with values up to 2, which doesn't make sense.

So I try to understand and I build a pivot table with the following dimensions:

service_point_name | customer_uid | transaction_uid

and the following expression (to start):

Avg(Aggr(count(DISTINCT transaction_uid),customer_uid))

To my surprise, on the 1st line with all dimensions unfolded I get a "4". And it goes on with values different than "1". How comes? I thought aggr evaluated the expression in the context of the chart, and would output "1" in every case. In the context of a specific counter, a specific customer, and (that's the point) a specific transaction, how can I get several transactions?

Thanks,

A

[swuehl]

Hi,

try

Avg(Aggr(count(DISTINCT transaction_uid),customer_uid,transaction_uid))

The aggr function is evaluted in the context of the charts dimension, but as I believe only with regard to the listed aggr dimension.

So if you don't list transaction_uid in your aggr dimension list, it will take all transactions into account for given customer_uid (given by charts dimension).

I guess that my above expression is not what you need for your issue, just to show the effect.

I think you might need to shift the total into the count like

Avg(Aggr(count(DISTINCT transaction_uid),customer_uid))/avg( Aggr(count(total DISTINCT transaction_uid),customer_uid))

A small example app might help to help you more.

Stefan