How to compute Weighted average in a comlicate aggr expresion ?

[yanivvl0]

hi to all ,

in my previos discussion , see  https://community.qlik.com/thread/232790?sr=inbox

i got a great answer from Sunny , look like that :

=Sum({<Key = {"=Aggr(Max(TOTAL <Id> Step), Id, Step) = 4 or Aggr(Max(TOTAL <Id> Step), Id, Step) > Step"}>}Value)

BUT we have another complexity , because the Value is a Weighted average  :

sum ( Id_value * Step_value ) / sum ( Id_value ) .

if we use the Weighted average as Value get a wrong result ,

need your Devoted help.  thanks.

[sunny_talwar]

What is the dimension on which the weighting is done?

[Gysbert_Wassenaar · Accepted solution]

If you're nesting aggregation functions, which in this case you want to, you need to use the aggr function.

Sum( {<...stuff...>} aggr( sum ( Id_value * Step_value ) / sum ( Id_value ), DimA, DimB, ... DimN) ). Replace DimA, DimB, ... DimN with the dimensions over which the average is weighted.

[sunny_talwar]

May be something along these lines

=Sum({<Key = {"=Aggr(Max(TOTAL <Id> Step), Id, Step) = 4 or Aggr(Max(TOTAL <Id> Step), Id, Step) > Step"}>} Aggr(Sum(Id_value * Step_value)/Sum(Id_value), <Dimensions>))

[yanivvl0]

hi gysbert  , the dimension is step - and its work fine - Big help , thanks !!

[yanivvl0]

its Step - again many thanks Sunny !!