topic Expression with two func aggregation in QlikView

Expression with two func aggregation

Tue, 26 May 2009 22:58:06 GMT

I need help:

i have the expression:

Avg(if(RTT<Avg(PTT)-1,RTT))

this is wrong, because isn't posible have 2 functions aggregation (AVG) in the same expression,

but i don't know is it.

RTT is a Real Transit Time (40,35,30,32,54)

PTT is a Planned Transit Time (41,35,31,29,40)

I need:

get Average of all RTT that are lesser that Average of PTT -1

some one Idea?????

Thanks in advance for help

Sergio.

Expression with two func aggregation

Oleg_Troyansky — Tue, 26 May 2009 23:16:15 GMT

In order to nest aggregation functions, you need to use "Advanced Aggregation" with function "Aggr". You also need to determine, at what level of detail do you want the "internal" aggregation to happen - i.e. you are comparing RTT with the average of PTT - at what level?

You expression should look like the following:


Avg( aggr( if(RTT<Avg(PTT)-1,RTT), <Dimension1, Dimension2...>) )

Oleg

Expression with two func aggregation

Tue, 26 May 2009 23:25:22 GMT

Will it work if you define a variable say AVGPTT as =Avg(Total PTT)

and use the following as Expression:

Avg(If(RTT<$(AVGPTT)-1,RTT))

Expression with two func aggregation

Tue, 26 May 2009 23:44:23 GMT

Thanks very much

but it isn't what i need becuase PTT and RTT are depending of Filters and Selections in Sheet

no the Avegare Total of PTT

Expression with two func aggregation

Wed, 27 May 2009 00:02:35 GMT

I have reviewed this function Aggr(), but the result isn't correct

becuase i don't know what is the level detail

i think that is ID of the Transit Time, but the result isn't correct

the fields RTT and PTT depending of Filters and Selecctions.

for example: if is selected a Port Origin or Port Destination or Country or Dates or others
the Averages must be with the values posibles

Best Regards,

Sergio

Expression with two func aggregation

Oleg_Troyansky — Wed, 27 May 2009 00:22:51 GMT

Sergio,

when everything is defined correctly, it should work. Function aggr() takes in account your selections.

You need to answer this question: "Avg(PTT)" - for what Entity? Average by Port? or Country? If you are after the overall average (for anything selected/associated), than the approach with the variable (suggested above) would work even better.

Oleg

Expression with two func aggregation

Wed, 27 May 2009 03:03:18 GMT

this is a file with the same problem

if you can see!

[View:/cfs-file.ashx/__key/CommunityServer.Components.PostAttachments/00.00.06.54.26/Two-Func-Aggregation.qvw]

Expression with two func aggregation

Anonymous — Wed, 27 May 2009 03:16:23 GMT

Sergio,
We could've seen it - if you could post an example.
I"m sure that aggr will work if you correctly define the dimensions which you need to use in aggregation. Sometimes it's not straightforward.