Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
Nope, it's makes nothing
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
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)
everething is A with it
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
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)
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