Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Expression with two func aggregation

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.

7 Replies
MVP
MVP

Expression with two func aggregation

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

Not applicable

Expression with two func aggregation

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

Not applicable

Expression with two func aggregation

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

Not applicable

Expression with two func aggregation

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

MVP
MVP

Expression with two func aggregation

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

Not applicable

Expression with two func aggregation

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]

mov
Esteemed Contributor III

Expression with two func aggregation

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.

Community Browser