5 Replies Latest reply: Aug 17, 2011 9:07 AM by Nicole A RSS

AGGR in a Pivot Table - Needs to be dynamic by dimension

Nicole A

Hi, I am wondering if there is a way to set an aggr function to be dynamic by dimension when a user expands/collapses columns in a Pivot Table.  I have my function working properly when I code the dimension I want to look at, but I need the formula to work on mulitple dimensions.  I thought in the aggr function I could just add the dimensions I want to possibly group by, but it will still only calc correctly at the lowest level, then sums the totals of each level instead of performing the function at each level.  Has anyone come across this issue & found a fix?  I don't know are unique, but basically everything we do here needs to be done in pivot tables with changing dimensions/filters. Here is my formula that is working correctly at the brand level, but when I collapse brand in my pivot, it adds all of the brand lines together instead of performing the calc at the Sub Category level.

=if(dimensionality(),sum(aggr(sum({<"Product/No"=,"Category"=,"Category/No"=, "Sub Category"=, "Sub Category/No"=, "Brand"=>} total <"STORE#"> AMOUNT*NetItemFlag),"STORE#","Category","Sub Category","Brand")),sum({$<"Store Depart Group"=, "Store Depart"=, Department/No=, Category/No=,         Sub Category/No=, Brand=, Vendor/No=, Product/No=, TENDER_TYPE_DESC=,         CASHIER_NAME=, STORE_UPC=, STORE#=P()>}TOTAL AMOUNT*NetItemFlag))

 

The portion that is not working correctly is

total <"STORE#"> AMOUNT*NetItemFlag),"STORE#","Category","Sub Category","Brand")

b/c it is always performing the aggr on Brand.
Thanks for any input.