10 Replies Latest reply: Sep 29, 2016 2:13 AM by Paul Steinborn

# Aggregation of Set Analysis

Dear Community,

i want to aggregate a set analyis like following:

Get SUM of COUNTED VALUES that are GREATER THAN a VALUE_LIMIT AGGREGATED OVER VALUE_GROUP

I tried follwoing without luck :

SUM(AGGR(COUNT({<VALUE= {">\$(= [VALUE_LIMIT])"}>} VALUE),VALUE_GROUP))

I read a lot of topics about smiliar problems, but nothing helped me.

Maybe the aggreagation should happen at a deeper level, inside the Set Analysis.

Any idea how to solve this?

• ###### Re: Aggregation of Set Analysis

This seems to look in the right direction, what exactly was the issue that you ran into?

• ###### Re: Aggregation of Set Analysis

Hi Sunny,

it gives always the result 0.

For example:

Value, Value Limit, Value Group

1, 2, x

3, 2, x

4, 2, x

2, 4, y

2, 4, y

3, 4, y

For this data the result should be 2 as only 2 Values are higher than their limit inside a group.

So counting it, it will give 2 for Value Group x and 0 for Value Group y.

Summing up the counts will still give the result of 2.

• ###### Re: Aggregation of Set Analysis

If see what you are trying to do. Set Analysis cannot do a row by row comparison. For this to happen you would need to either use If() function or create a flag in the script.

Option1

Sum(If(Value >= [Value Limit], 1, 0))

Option2

If(Value >= [Value Limit], 1, 0) as Flag

and then on the front end, you can do this

Sum(Flag)

• ###### Re: Aggregation of Set Analysis

Yes i thought about that, but how to aggeregate than on Value Group?

• ###### Re: Aggregation of Set Analysis

You can do like this -

SUM(AGGR(Sum(Flag),[Value Group]))

As Sunny suggested, script like -

If(Value >= [Value Limit], 1, 0) as Flag

inline [

Value, Value Limit, Value Group

1, 2, x

3, 2, x

4, 2, x

2, 4, y

2, 4, y

3, 4, y ]

• ###### Re: Aggregation of Set Analysis

What is the benefit of doing Sum(Aggr(Sum(Flag),[Value Group]))? It will equal Sum(Flag). Do you mean one of these?

Sum(Aggr(Sum(DISTINCT Flag),[Value Group]))

or

Sum(Aggr(Avg(Flag),[Value Group]))

or

Sum(Aggr(Only(Flag),[Value Group]))

• ###### Re: Aggregation of Set Analysis

Hi!

we are useing Option1

and with this its finally working!

SUM(AGGR(Sum(If(Value >= [Value Limit], 1, 0)),[Value Group]))

Thanks to all for your support!

• ###### Re: Aggregation of Set Analysis

I think Its look correct

• ###### Re: Aggregation of Set Analysis

It does not look VALUE_LIMIT is a variable?

if you have a variable defined like

set VALUE_LIMIT=300 then your syntax should work else try something like below

SUM(AGGR(COUNT({<VALUE= {">\$(=Max( [VALUE_LIMIT]))"}>} VALUE),VALUE_GROUP))

hth

Sasi

• ###### Re: Aggregation of Set Analysis

Hi Sasi,

you solution is working if one group is selected. While selecting multiple groups to show the sum of each group for example inside a table each sum will be = 0