Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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))
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
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
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
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]
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.