AGGR()

[Anonymous]

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

[Clever_Anjos]

Maybe

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

[sunny_talwar]

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

[MK_QSL]

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

[sunny_talwar]

How about this:

Tot Purchases

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

Purchase %

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

[MK_QSL]

Great !

[Anonymous]

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?

[sunny_talwar]

May be like this:

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

,0.25), Category, Supplier))

or

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

,0.25), Category, Supplier))

[Anonymous]

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)

[sunny_talwar]

Opps, My bad,

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

or

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