Aggregation of Set Analysis

[Anonymous]

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?

[sunny_talwar]

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

[qlikview979]

I think Its look correct

[Anonymous]

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.

[sunny_talwar]

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)

[sasiparupudi1]

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

[Anonymous]

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

[Anonymous]

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

[Digvijay_Singh]

You can do like this -

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

As Sunny suggested, script like -

Load *,

  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 ]

[sunny_talwar]

digvijay‌ -

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