Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
evgeniystuchalk
Not applicable

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:

120122013
Sum (Delta)ABCSum (Delta)ABC
Moris80000A5000C
Doris7500B80000A
Boris7500B7500B
Norris5000C7500B

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?

1 Solution

Accepted Solutions
marcus_sommer
Not applicable

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

10 Replies
miguelbraga
Not applicable

Re: stuck with aggr function

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
Not applicable

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

evgeniystuchalk
Not applicable

Re: stuck with aggr function

Nope, it's makes nothing

evgeniystuchalk
Not applicable

Re: stuck with aggr function

with aggr function in measure i see only one ABC value

06dfbef5e9.jpg

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


3592a4c152.jpg

simondachstr
Not applicable

Re: stuck with aggr function

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
Not applicable

Re: stuck with aggr function

everething is A with it

b293316467.jpg

marcus_sommer
Not applicable

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

evgeniystuchalk
Not applicable

Re: stuck with aggr function

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

39d50c33f7.jpg

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
Not applicable

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