topic Re: Aggr() expression? in QlikView

Aggr() expression?

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone.

I think I'm missing something with the aggr() function.

To explain it shortly, I want to compare the amount of sales done by each salesman in each country with the amount of sales done in each country by all salesmen. (by month, year and past 12 months).

I also wish that any selection (despite MonthYear) won't alter the result of the "€ Month (all Salesmen)" column.

In the app joined, I wish I could have the same values from "COUNTRY' pivot table

in the "€ Month (all Salesmen)" column of the "SALES by Country" pivot table.

Many thanks for any help provided.

David.

Re: Aggr() expression?

sunny_talwar — Mon, 01 Aug 2016 15:35:59 GMT

Isn't that what you already have? I am not sure what is missing here:

Re: Aggr() expression?

Oleg_Troyansky — Mon, 01 Aug 2016 15:42:16 GMT

Hi David,

there is a very long explanation for the reasons why you don't need an AGGR() function in this case, and how to make it work properly. If you even make it to the Masters Summit for Qlik, you can listen to my 4-hour lecture that covers that, among other advanced AGGR() and Set Analysis issues. If you can't make it to the Masters Summit, you can read about it in my book QlikView Your Business.

The short version is this (you'll have to take my work for it though):

1. No need to use AGGR() - a simple sum(TOTAL<Country> Amount) with the same Set Analysis condition will do the job.

2. You don't see exactly the same numbers because of your calculated Dimensions - if you clear the box "Suppress when Value is Null", you will see that some of the amounts are attributed to Country=NULL. That happens when one of your nested IF conditions (by the way, really-really bad for performance) render FALSE. I'd recommend to remove the IF conditions and achieve the same effect in a different way. I wasn't sure what exactly is it supposed to do...

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Austin, Texas and Johannesburg, South Africa!

Re: Aggr() expression?

Mon, 01 Aug 2016 15:43:02 GMT

Sorry, bad copy/paste.

Of course the values are here, but I wish them not to depend on any salesman man selection.

Re: Aggr() expression?

Mon, 01 Aug 2016 15:52:12 GMT

Hi Oleg.

Wish I could join the summit but it won't be possible.

Plus, I don't think I have enough skills in both english and Qlik to attend it.

I know I can obtain these result without Aggr() but I don't see another way not to be dependant of any selection on the Salesman field.

Thanks.

Re: Aggr() expression?

Oleg_Troyansky — Mon, 01 Aug 2016 16:22:54 GMT

Hi David,

once you use {1} in your Set Analysis , you disregard any selections. Alternatively, you can use the following to disregard just the selection of Salesman:

{$<Salesman=>}

AGGR() doesn't add any extra value in terms of disregarding the selection of a Salesman.

Re: Aggr() expression?

Tue, 02 Aug 2016 07:45:19 GMT

Hi Oleg.

Ok I think I'm trying to achieve something not possible the way I tried it or I haven't been clear enough sorry.

So let's ask the question differently,

From the beginning :

1) I want to compare, for a selected MonthYear, the amount of sales done by each salesman in each country with the amount of sales done in each country by all salesmen. (by month, year and past 12 months).

2) also, that any selection (despite MonthYear) won't alter the result of the "€ Month (all Salesmen)" column.

3) and, to hide other Salesmen rows when one (or more) is selected.

So this is the pivot table (with no calculated dimensions) :

Two dimensions : Salesman, Country

Three Expressions :

- € Month : =sum({<MonthYear={'$(=Only(MonthYear))'}>} Amount)

- € Month (AllS) : =sum(TOTAL <Country> {$<Salesman=, MonthYear={'$(=Only(MonthYear))'}>} Amount)

- AGGR : =sum(TOTAL <Country> {1<MonthYear={'$(=Only(MonthYear))'}>} aggr(sum(TOTAL <Country> {1<MonthYear={'$(=Only(MonthYear))'}>} Amount), Country))

At that point I wish to keep these expression values despite dimensions are reduced or not like here :

And, if a salesman is selected, to hide the other ones :

Is this even possible?

Thanks.

Re: Aggr() expression?

Oleg_Troyansky — Thu, 04 Aug 2016 20:14:30 GMT

Yes, it's certainly possible. I just don't have as much time to delve into all the details of this calculation.

I think you can find some hints to the solution in this blog article:

http://www.naturalsynergies.com/q-tip-8-ranking-the-unrankable/

cheers,

Oleg Troyansky

Re: Aggr() expression?

rajuamet — Fri, 05 Aug 2016 10:34:26 GMT

Hi DAVID MALAIZIER,

I have aggregated the amount -> country and month year wise to get what you asked. What I am providing is an idea for your requirement. See the script. Hope this helps

PFA.