
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem aggr function
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess use set analysis to ignore the account?
sum(Amount)/sum(total aggr(avg({<Account=>} AmountD),Store))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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???

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess use set analysis to ignore the account?
sum(Amount)/sum(total aggr(avg({<Account=>} AmountD),Store))
