stuck with aggr function

[evgeniystuchalk]

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:

Client	ABC	Sum(Delta)
Moris	A	160000
Doris	B	15000
Boris	B	15000
Norris	C	10000

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?

[miguelbraga]

Maybe this will help you:

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)

Put the year in your table and adjust as you wish

Hope this helps you.

Regards,

MB

[marcus_sommer]

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

[evgeniystuchalk]

Nope, it's makes nothing

[evgeniystuchalk]

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

[simondachstr]

You have to add the Year to your 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)

[evgeniystuchalk]

everething is A with it

[marcus_sommer · Accepted solution]

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

[evgeniystuchalk]

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)

[marcus_sommer]

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