Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Marko,
Did you test:
Sum(aggr(sum({<Date_MonthAsNumber = {1}>} [Ratio])*sum([Sales Amount]),SalespersonCode,CustomerCode,ItemGroupCode))
Gabriel
Marko,
Did you test:
Sum(aggr(sum({<Date_MonthAsNumber = {1}>} [Ratio])*sum([Sales Amount]),SalespersonCode,CustomerCode,ItemGroupCode))
Gabriel
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.
Great! It worked! Thank you a bunch!
🙂