topic Re: AGGR() in QlikView

AGGR()

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi my name is Andrea,

I would like to understand how to solve this issue.

Category	Supplier	Purchase	Tot Purchase	Purchase %
1	A	1000	6000	16%
1	B	2000	6000	34%
1	C	3000	6000	50%
Tot		6000		100%
2	D	1000	3000	33%
2	E	2000	3000	67%
Tot		3000		100%

If i use this formula: aggr(NODISTINCT sum(Purchase),Category) i can get the column "Tot Purchase" and "Purchase %" as in the table. But if i click on a supplier (for example supplier A) the Tot Purchase becomes 1000 and Purchase % becomes 100%.

Is there any solution for this issue?

I would like to click on a supplier and mantain the same result.

Thanks to all in advanced.

Andrea

Re: AGGR()

Clever_Anjos — Wed, 08 Jun 2016 12:12:01 GMT

Maybe

aggr(NODISTINCT sum({<Supplier>}Purchase),Category)

Re: AGGR()

sunny_talwar — Wed, 08 Jun 2016 12:55:15 GMT

Can you try these expressions

Tot Purchases

Sum(TOTAL <Category> {<Supplier>} Purchases)

Purchase %

Sum({<Supplier>} Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

Update: I would make sure that Purchase % expression is not set to relative. In other words, uncheck relative for Purchase % expression

Re: AGGR()

MK_QSL — Wed, 08 Jun 2016 13:05:59 GMT

Sunny, If we use Total Qualifier with {<Supplier>}, we would not be able to use filter in chart..

Re: AGGR()

sunny_talwar — Wed, 08 Jun 2016 13:09:07 GMT

How about this:

Tot Purchases

Sum(TOTAL <Category> {<Supplier>} Purchases) * Avg(1)

Purchase %

Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

Re: AGGR()

MK_QSL — Wed, 08 Jun 2016 13:11:37 GMT

Great !

Re: AGGR()

Anonymous — Wed, 08 Jun 2016 13:53:37 GMT

Thanks to all,

If i would like to calc the Fractile of the following formula

Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

Can i simple make this:

Fractile(

Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

,0.25)

or i have to do introduce other things in order to calc the same result for any selected supplier?

Re: AGGR()

sunny_talwar — Wed, 08 Jun 2016 15:09:38 GMT

May be like this:

Fractile(TOTAL <Category> Aggr(Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

,0.25), Category, Supplier))

Fractile(TOTAL Aggr(Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases)

,0.25), Category, Supplier))

Re: AGGR()

Anonymous — Wed, 08 Jun 2016 15:50:12 GMT

I tried with both but it give to me error in the expression. I would like to ask about last piece of expression:

, Category, Supplier))

These are the arguments of which expression? Fractile? because i thought that Fractile function end with ,0.25)

Re: AGGR()

sunny_talwar — Wed, 08 Jun 2016 16:00:23 GMT

Opps, My bad,

Fractile(TOTAL <Category> Aggr(Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases), Category, Supplier), 0.25)

Fractile(TOTAL Aggr(Sum(Purchases)/Sum(TOTAL <Category> {<Supplier>} Purchases), Category, Supplier), 0.25)

Re: AGGR()

Anonymous — Wed, 08 Jun 2016 16:23:18 GMT

Thanks for the answer.....this comumunity is awesome!

I Will try this solution as soon as i can.

Could you please explain me the difference in using <category > and {<category>} ?

Re: AGGR()

sunny_talwar — Wed, 08 Jun 2016 16:40:53 GMT

<category> is used with TOTAL qualifier to tell the expression that the Total need to be calculated for each category.

{<category>} is a set analysis syntax(similar to {<category =>} used to ignore selections in category field.

Re: AGGR()

Anonymous — Thu, 09 Jun 2016 10:59:45 GMT

Hi at the end i use this:

fractile(total <Categoria> {<[Ragione sociale]>} Aggr(Sum({<[Ragione sociale]>}Acquistato)/Sum(TOTAL <Categoria> {<[Ragione sociale]>} Acquistato), Categoria,[Ragione sociale]),0.25)

Where:

Categoria = Category

Ragione sociale = Supplier

Acquistato = Purchase

It's look like good because now when i select a supplier the relative fractile is correct

Re: AGGR()

sunny_talwar — Thu, 09 Jun 2016 12:07:41 GMT

Awesome, I am glad you were able to figure it out .

If you got everything you were looking for, I would ask you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny