Aggr pivot table not showing sums

[#1]

Hi.

I have a question regarding using set analysis in pivot tables.

Story goes like this: budgeted sales amount for groups of items are usually entered for 01.01. every year and ratios are entered for months. I need to get in a pivot table following:

(Salesperson - Customer - Item group) - (Sales for January, Sales for februrary, Sales for March...)

aggr(sum({<Date_MonthAsNumber = {1}>} [Ratio])*sum([Sales Amount]),SalespersonCode,CustomerCode,ItemGroupCode)

does not work (totals are not calculated , or are calculated but not for every combination of dimension).

Thanks,

Marko

[GabrielAraya · Accepted solution]

Marko,

Did you test:

Sum(aggr(sum({<Date_MonthAsNumber = {1}>} [Ratio])*sum([Sales Amount]),SalespersonCode,CustomerCode,ItemGroupCode))

Gabriel

[#3]

Same problem here. We didn't solved it in pivot table, but normal table. On formular tab check totals sum (of y-values) on formular for all values. It seams that QV has some problems with the totals in pivot table while not using simple sum() statements.

[#4]

Great! It worked! Thank you a bunch!

🙂