# 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.
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:

 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?

1 Solution

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

## 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

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

Not applicable

## Re: stuck with aggr function

Nope, it's makes nothing

Not applicable

## 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

Not applicable

## 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)

Not applicable

## Re: stuck with aggr function

everething is A with it

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

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.

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)

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

Community Browser