topic Problem aggr function in QlikView

Problem aggr function

Fri, 15 Oct 2010 22:40:31 GMT

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.

Problem aggr function

johnw — Fri, 15 Oct 2010 23:07:47 GMT

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.

Problem aggr function

Fri, 15 Oct 2010 23:37:04 GMT

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).

Problem aggr function

johnw — Fri, 15 Oct 2010 23:54:26 GMT

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))

Problem aggr function

Sat, 16 Oct 2010 00:16:52 GMT

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.

Problem aggr function

johnw — Sat, 16 Oct 2010 00:33:27 GMT

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???

Problem aggr function

Sat, 16 Oct 2010 00:54:11 GMT

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?

Problem aggr function

johnw — Sat, 16 Oct 2010 00:58:57 GMT

I guess use set analysis to ignore the account?

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