10 Replies Latest reply: Sep 5, 2016 1:06 PM by Evgeny Stuchalkin

# stuck with aggr function

Hello! I'm using this expression for calculaion of ABC analysis dimension for client base:

Aggr({<Client>}

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc1, 'A',

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc2, 'B', 'C')),
Client)

Works fine, but only in table with ont Dimension. Result looks like:

ClientABCSum(Delta)
MorisA160000
DorisB15000
BorisB15000
NorrisC10000

I want also to see, how ABC segmet changes from year to year for every client. Like this:

 1 2012 2013 Sum (Delta) ABC Sum (Delta) ABC Moris 80000 A 5000 C Doris 7500 B 80000 A Boris 7500 B 7500 B Norris 5000 C 7500 B

Just cant to get any result. Only to display ABC segment for current Year. If selected more than one year, ABC calculated for all amounts of Delta, ignoring the years. How to fix it?

• ###### Re: stuck with aggr function

Aggr({<Client=, Year=>}

If((Rank(Sum({<Client=, Year=>} Delta),1)-1) / Count({<Client=, Year=>} distinct total  Client)< abc1, 'A',

If((Rank(Sum({<Client=, Year=>} Delta),1)-1) / Count({<Client=, Year=>} distinct total  Client)< abc2, 'B', 'C')),
Client)

Hope this helps you.

Regards,

MB

• ###### Re: stuck with aggr function

Nope, it's makes nothing

• ###### Re: stuck with aggr function

You need to include all (relevant) dimensions within your aggr-function  - in this case client and year whereby I think you didn't need the aggr in this case and:

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc1, 'A',

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc2, 'B', 'C'))

should be enough.

- Marcus

• ###### Re: stuck with aggr function

with aggr function in measure i see only one ABC value

without aggr function in measure i see ABC value, that not correct for years

• ###### Re: stuck with aggr function

Aggr({<Client>}

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc1, 'A',

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total  Client)< abc2, 'B', 'C')),
Client, Year)

• ###### Re: stuck with aggr function

everething is A with it

• ###### Re: stuck with aggr function

Try:

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total <Year> Client)< abc1, 'A',

If((Rank(Sum({<Client>} Delta),1)-1) / Count({<Client>} distinct total <Year> Client)< abc2, 'B', 'C'))

- Marcus

• ###### Re: stuck with aggr function

Thanks! It works. But now because of set analysis expression {<Client>} i cant filter clients in this table.

When i'm selecting one, other clients remain in table, only ther Delta measure goes to zero. i'm using this set expression for ABC segmentation remains same on any clients selection. Without it, ABC recalculated every time i'm selecting different clients. For example, if you select one C client, he became A client, because he is only one)

• ###### Re: stuck with aggr function

You might need to add 1 as set identifier:

If((Rank(Sum({1<Client>} Delta),1)-1) / Count({1<Client>} distinct total <Year> Client)< abc1, 'A',

If((Rank(Sum({1<Client>} Delta),1)-1) / Count({1<Client>} distinct total <Year> Client)< abc2, 'B', 'C'))

Maybe also this might be helpful: Recipe for an ABC Analysis.

- Marcus

• ###### Re: stuck with aggr function

Found temporary solution with sorting formula and conditional formatting

Recipe for an ABC Analysis dont answer last question. But thank you, you have helped me a lot.