Problem aggr function

[#1]

Hello, everybody. I´m trying to aggregate AmountD (Driver), but I need average for months, and sum for other dimmensions. I should get 134 for all acounts in the attached doc. Please, I need some help.

[johnw]

I don't understand the question. You already have 134 in the attached doc. Did you want to be able to deselect everything and still see 134? Did you want some sort of chart with a total line that's 134?

What you say sounds kind of like sum(aggr(avg(AmountD),Month)), but that's very similar to your current expression. It does produce 134 with your current selections, but not if you clear them.

Not really sure what you want.

[#3]

Thanks for the answer John, what I need is to divide all accounts by the number of employees (or the driver I choose). Now for energy I have 154, and Gas 113, all must be divided by 134 (the number of employees of store A+B).

[johnw]

Still not sure I'm following, but if I am, what you want might be this expression added to your chart?

sum(aggr(avg(AmountD),Store,Account))
/sum(total aggr(avg(AmountD),Store))

[#5]

I have an extra column for the ratio. I would like to show first the number of employees (AmountD) and then the ratio=Amount/AmountD. Something like this:

Account	Amount	Driver	AmountD	Ratio
Energy	1,370	Employee	134	10.22
Water	2,104	Employee	134	15.70
Gas	2,131	Employee	134	15.90

When I replace your formula in Ratio Expression I get other numbers. Thanks for your help.

[johnw]

OK, I thought you wanted for Energy, 154/134, for Water, 134/134 and for Gas, 113/134. Instead you want for Energy, 1370/134, for Water 2104/134 and for Gas, 2131/134?

Same idea, just a different expression on top, the one you used for Amount instead of AmountD:

sum(Amount)
/sum(total aggr(avg(AmountD),Store))

However, it looks like you've already solved it, since your chart shows the numbers you want???

[#7]

Thks John, It´s almost right, but now the numbers shouldn´t change when I make any selection in accounts. How can I do that?

[johnw · Accepted solution]

I guess use set analysis to ignore the account?

sum(Amount)/sum(total aggr(avg({<Account=>} AmountD),Store))