Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

biob_stein
Contributor

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?

10 Replies

Re: Aggregation of Set Analysis

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

qlikview979
Valued Contributor

Re: Aggregation of Set Analysis

I think Its look correct

biob_stein
Contributor

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)

sasiparupudi1
Honored Contributor III

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



biob_stein
Contributor

Re: Aggregation of Set Analysis

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

biob_stein
Contributor

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

Digvijay_Singh
Honored Contributor III

Re: Aggregation of Set Analysis

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 ]

Re: Aggregation of Set Analysis

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

Community Browser