Help regarding AGGR function

[qlikmsg4u]

!Hello Forum,

Can any one explain why the following expression working in Pivot table not in straight table.

Sum(Sales)/Max(total aggr(sum(Sales),Country,City))

Here is the sample data:

LOAD * INLINE [

  Country, City, Sales

  USA, San Diego, 24567

  USA, Dallas, 54962

  USA, New York, 67013

  USA, Boston, 45824

  UK, London, 64002

  UK, Birmingham, 44291

  UK, Manchester, 40320

  Germany, Berlin, 52912

  Germany, Frankfurt, 61832

  Germany, Munich, 35812

  Japan, Tokyo, 42137

  Japan, Yokohama, 55832

  Japan, Osaka, 37643

];

[tresesco · Accepted solution]

Uncheck 'Relative' in expression tab, and check 'Show in Percent' in number tab.

[qlikmsg4u]

can you tell me what is the difference between relative and show in %?

[nagaiank]

Seems to work both in pivot table and straight table.

[francescopuppin]

Hi guys,

thanks for this post, I had a similar problem, and this was helpful!

I still have a small doubt: I have noticed that, if you remove Country from the chart, the formula fails: it gives the weight against the grand total.

Is there a way to make it work also when Country is removed?

I have tried this one, but it does not work:

Sum(Sales) / Sum(TOTAL<Country> Aggr(Sum(Sales),Country))

If you have the correct formula, please let me know!

Thanks!!!