topic Re: Aggregate over dimensions in QlikView

Aggregate over dimensions

Tue, 13 Oct 2015 14:34:28 GMT

Hi I'm trying to get the next scenario,

The info:

Country, Sales

Brasil, 10000

USA, 15000

Spain, 5000

So I want to get the percentage of each country over the country with the highest sales, in this case

Brasil,10000, 66%

USA,15000, 100%

Spain,5000, 33%

I've tried sum(Sales)/ max(aggr(sum(Sales),Country)) but I only get the same value as a regular SUM

Any Ideas?

Re: Aggregate over dimensions

Clever_Anjos — Tue, 13 Oct 2015 15:48:33 GMT

Try

sum(Sales)/ sum(TOTAL Sales)

Re: Aggregate over dimensions

SreeniJD — Tue, 13 Oct 2015 15:51:49 GMT

This formula gives the correct solution, however you might need to have another dimension like period and product which provides more accurate scenario.

Sum(Sales)/

Max(aggr(Sum(Sales),Country))

Lets say for Year 2015

sales would be .... sum(Sales)/Max(aggr(Sum(<Year={2015}>),Product,Country)

Sreeni

Note: You might try with TOTAL function as well..

Re: Aggregate over dimensions

Tue, 13 Oct 2015 15:57:56 GMT

Thank you , but that expression gives me the % over the total amount of sales and what i want is the % over the Max amount of any country

Re: Aggregate over dimensions

Tue, 13 Oct 2015 18:01:58 GMT

Ok thank you, but if I don't have any extra dimension?

For a textbox I get the right value with the expression that I posted, but I need it in a straight table as a column, If I use other more specific dimension i don't get the right value.

I posted an excel file of what I want

Thank you

Re: Aggregate over dimensions

Clever_Anjos — Tue, 13 Oct 2015 18:05:15 GMT

Sorry for my misunderstanding

try this

sum(Sales)/ max(total aggr(sum(Sales),Country))

Re: Aggregate over dimensions

Digvijay_Singh — Tue, 13 Oct 2015 18:07:56 GMT

Sum(Sales) / Max(Total Aggr(Sum(Sales), Country))

Re: Aggregate over dimensions

maxgro — Tue, 13 Oct 2015 18:16:19 GMT

sum(Sales) / max(total Sales)

Re: Aggregate over dimensions

Tue, 13 Oct 2015 21:15:39 GMT

Thanks everybody was very helpfull