Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
virat_jb
Contributor II
Contributor II

Aggr function with Hierarchy group

Hi,

I am using a Aggr function on a dimension that I want it to dynamically change based on the Hierarchy selection from the Group that I created. Below is an example:

I have these two tables with fields like this:

Country | Sales | Margin

Region | Sub-Region | Country


Both these tables are associated by Country field and I have Geography group that has grouping of Region, Sub Region and Country. Now I am putting a (TreeMap) chart by the Geography group that showing Margin%. I am looking a function like this to use:


Aggr( sum(Margin) / sum(Sales), Geography)


and I want the Aggr function to work based on the Geography group level selected on the chart. If I define the Aggr() at Country level then I am not getting the desired output of Margin% for Region and Sub region level group selection. (it is giving a simple sum of all countries Margin% at the higher level).

Can someone please advice how I can make the Aggr() work with grouped dimension?

Thanks,

Balaji

2 Replies
OmarBenSalem
Partner
Partner

What happens if you only have sum(Margin) / sum(Sales) as an expression? without the aggr?

virat_jb
Contributor II
Contributor II
Author

Hi Omar,

The actual expression I am trying to do require the Aggr() function to do a Product level and Geography level. Where Product is a single dimension while Geography is a grouped dimension. The above scenario I tried to give a simple form of what I am looking for something like this (still simplified but where the Grouped dimension base Aggr() is needed).

Sum(Aggr( (Sum(Margin)/Sum(Sales)) * Sum(Qty), Product, Geography) / Sum(Qty)

Thanks,

Balaji