why are my totals incorrect? both pivot and straight table?

[amien]

SUM({<Code = {"41"}, isFilter = {"1"}>} Costs)

gives me a wrong total ..

BUT

SUM(AGGR(SUM({<Code = {"41"}, isFilter = {"1"}>} Costs),MonthMM))

Gives me a correct total.. values for each month are all the same ..

what's wrong?

Total mode is on expression (grayed out)

[swuehl · Accepted solution]

The values for each month are all the same? Is this what you expect?

If not, I assume that you have not correctly linked the MonthMM containing table to your Costs table, so you get a total on each line as well as for the total row. Is this what you see, if you say "wrong total"?

The sum(aggr( sum (... )), MonthMM) is calculating the sum of rows then, while your first expression in total row will be evaluated in global context, like in a text box.

Not sure if I make myself clear, but maybe you could clarify what you see and expect to see in a bit more detail.

[amien]

Thanks for your reply again swuehl

Yes, i expect to be that all values are the same. The values on period level are fine.. It's just that the total is not correct.

[johnw]

As Stefan says, sum(aggr(sum(...))) is doing a sum of rows.  Sum of rows is often different than a straight sum.  For instance:

Months:
Key, MonthMM
1, Jan
2, Jan
2, Feb

Costs:
Key, Costs
1, 100
2, 100

MonthMM sum(Costs) sum(aggr(sum(Costs),MonthMM))
Jan     200        200
Feb     100        100
Total   200        300

Hopefully you can see how QlikView calculates all of these numbers.

So basically, if the second expression gives you what you wanted, then you wanted a sum of rows, and keep using the second expression.  The first expression doesn't give you what you want because it's overly simplistic for your case.

[amien]

Clear . Thanks both