topic Aggregation of Set Analysis in QlikView

Aggregation of Set Analysis

Anonymous — Wed, 28 Sep 2016 11:54:05 GMT

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

sunny_talwar — Wed, 28 Sep 2016 12:00:15 GMT

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

Re: Aggregation of Set Analysis

qlikview979 — Wed, 28 Sep 2016 12:14:09 GMT

I think Its look correct

Re: Aggregation of Set Analysis

Anonymous — Wed, 28 Sep 2016 12:55:04 GMT

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

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

sunny_talwar — Wed, 28 Sep 2016 12:59:54 GMT

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

sasiparupudi1 — Wed, 28 Sep 2016 13:02:07 GMT

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

Anonymous — Wed, 28 Sep 2016 13:16:41 GMT

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

Re: Aggregation of Set Analysis

Anonymous — Wed, 28 Sep 2016 13:21:49 GMT

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

Re: Aggregation of Set Analysis

Digvijay_Singh — Wed, 28 Sep 2016 13:46:41 GMT

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

3, 4, y ]

Re: Aggregation of Set Analysis

sunny_talwar — Wed, 28 Sep 2016 17:20:17 GMT

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

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

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

Re: Aggregation of Set Analysis

Anonymous — Thu, 29 Sep 2016 06:13:03 GMT

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!