Expression with two func aggregation

[#1]

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.

[Oleg_Troyansky]

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

[#3]

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

[#4]

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

[#5]

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

[Oleg_Troyansky]

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

[#7]

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]

[Anonymous]

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.