how to write dynamic expression for weighted avg

[kunkumnaveen]

HI

sample data:

place        ship     quantity       tm

loadport  ship1      1000           9.2

loadport  ship1      1200           9.8

loadport  ship1      1030           7.2

loadport  ship2    2000              1.5

loadport  ship2     1100           1.9

loadport  ship2    3000        9.2

required out put in straight table:

             place       ship           quantity                                tm

                                              sum(quantity)                      ?(how to write this dynamic expression)

             loadport    ship1            3230                                1000*9.2+1200*9.8+1030*7.2/1000+12000+1030

            loadport       ship2            6100                               2000*1.5+1100*1.9+3000*9.2/2000+1100+3000

thanks

[sunny_talwar]

Have you tried this expression:

Sum(quantity * tm)/Sum(quantity)

place and ship as dimensions

UPDATE: Sample attached

[avinashelite]

try like this

sum( quantity * tm)/sum(quantity )

[kunkumnaveen]

Hi,

i have tried like this :

aggr(sum({<PLACE={'LOAD PORT'},SHIP_STATUS={1}>}QTY_DAILY*TM)/sum({<PLACE={'LOAD PORT'},SHIP_STATUS={1}>} QTY_DAILY),USI)

is it necessary or not to have aggr function ?

seems it will work without aggr function......

[kunkumnaveen]

Hi

i tried by using aggr function

aggr(sum({<PLACE={'LOAD PORT'},SHIP_STATUS={1}>}QTY_DAILY*TM)/sum({<PLACE={'LOAD PORT'},SHIP_STATUS={1}>} QTY_DAILY),USI)

[avinashelite]

in this case you need to have the USI as the dimension .....otherwise it will not work ...

What is your exact requirement

[sunny_talwar]

Have you tried this?

Sum({<PLACE={'LOAD PORT'}, SHIP_STATUS={1}>}QTY_DAILY*TM)/Sum({<PLACE={'LOAD PORT'}, SHIP_STATUS={1}>} QTY_DAILY)

[kunkumnaveen]

HI

YES i am showing

PLACE,USI as dimensions

tm,quantity will be my expression

   place       ship                                quantity                                        tm

                                                     sum(quantity)                     

             loadport    ship1                    3230                               

            loadport       ship2                   6100                              

[kunkumnaveen]

so u mean no need to use aggr function .right,

i thought like we need to group the common ship,thats why i wrote aggr function

[avinashelite · Accepted solution]

so the above expression which your using will aggregate the data based on the USI and it will not consider the PLACE ....if that is your requirement than go ahead use your expression

OR

if you need the results based on the PLACE and USI then remove the aggr function and use rest of the expression it will work