Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

5 Replies
Not applicable
Author

I faced a similar situation. But I shall need a sample example from your understand it better.

Please write to me with a replica of your application.

Regards,

Bikash

Not applicable
Author

Thanks for your help.  Here's a sample app.

Not applicable
Author

I have been able to use the dimensionality() function to define the subtotals aggr calc at each level.  I am currently struggling with getting it to work with filters.  I thought my set analysis would address this, and I even tried {1} to ignore selections, and the filters are still not working as I had hoped.

Does anyone know if there are limitations of using set analysis with the aggr function?

Bikash, does this sound similar to the issue you faced?

Thanks again for any insight.  I revised my samlple with the inmproved dimensionality function.

Not applicable
Author

Hi Nicole,

I need some clarification. Can you please send an excel file with the expected percentage results.

Thanks,

Bikash

Not applicable
Author

I want the percentages to stay the same as they are with no filters selected, as when a user selects 1 item or category for example.  I got some help yesterday on here with using the {1} exclusion (I guess what I needed in my app was to exclude all selections, except date...date=P(date)) in set analysis that allowed me to combine that with dimensionality() at each level to get what I need.  I attached a sample with this logic if you want to take a look.  I would be curious to know if this is the same route you took, or if there is another way   The limitation I have with this approach (dimensionality) is that the users cannot change dimensions on the pivot table, and they are used to doing that.