Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

how to write dynamic expression for weighted avg

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

                                      



1 Solution

Accepted Solutions
avinashelite

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

View solution in original post

16 Replies
sunny_talwar

Have you tried this expression:

Sum(quantity * tm)/Sum(quantity)

place and ship as dimensions

UPDATE: Sample attached

Capture.PNG

avinashelite

try like this

sum( quantity * tm)/sum(quantity )

kunkumnaveen
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

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