Qlik View: Get Values WHERE (value2 = max)

Question

i have a relativly simple problem:

i have a Dataset that consists of an id(not for entry but for specific object), an age of the object and a power value.

So what i get is a lot of entries where there is a power at a specific age for a specific object.

I want to create a diagram that shows the average of all power values at the highest age over all objects(ids).

In SQl this basically would look something like SELECT power WHERE max(age).

Can anybody suggest a smart way how to this in a smart way in qlik view?

I already tried using the sum() function with total and aggr it over all ids but i keep getting weird results.

I tried using set analysis with aggr ({} power, id) but it doesnt work.

Edit: I tried

aggr(if (age= max(age), power), id)

but as soon as i select an id with more than one entry (different ages) there is no data displayed. Same when i remove the aggr function.

And:

Avg({$<age = max(age)>}Power)

Displays nothing at all (it also displays an error)

Also tried: Sum({$ <age= {$(=max(age))} > } power )

Still nothing.

Thanks Julian

It should work with set analysis, can you add your statement here? — jclozano, Jan 29 '15 at 18:03

score 1 · Accepted Answer · answered Feb 06 '15 at 14:32

1

Solved it with firstordervalue:

avg(aggr(firstsortedvalue (power, -age), id))

answered Feb 06 '15 at 14:32

Julian

score 0 · Answer 2 · answered Feb 01 '15 at 16:59

0

Yes Set Analysis should work. Something like:

Avg({$<age = max(age)>}Power)

Alternatively, you can use a conditional sum as well:

if (age = max(age), avg(Power))

Aggr is used to run a statistic over a list of records with a 'group by' condition as in SQL

answered Feb 01 '15 at 16:59

Atif Shaikh

If i do that, how will i have the avg Power over all Ids? As I said i have repeating Ids that stand for a spefic Object and I want to build an average over all power values at the maxium age of every Object. – Julian Feb 05 '15 at 14:55

2 Answers2