topic Re: how to write dynamic expression for weighted avg in QlikView

how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 10:49:50 GMT

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

Re: how to write dynamic expression for weighted avg

sunny_talwar — Tue, 08 Mar 2016 10:53:58 GMT

Have you tried this expression:

Sum(quantity * tm)/Sum(quantity)

place and ship as dimensions

UPDATE: Sample attached

Re: how to write dynamic expression for weighted avg

avinashelite — Tue, 08 Mar 2016 11:00:16 GMT

try like this

sum( quantity * tm)/sum(quantity )

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 11:13:24 GMT

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

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 11:14:08 GMT

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)

Re: how to write dynamic expression for weighted avg

avinashelite — Tue, 08 Mar 2016 11:18:08 GMT

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

What is your exact requirement

Re: how to write dynamic expression for weighted avg

sunny_talwar — Tue, 08 Mar 2016 11:20:55 GMT

Have you tried this?

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

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 11:23:31 GMT

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

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 11:26:44 GMT

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

Re: how to write dynamic expression for weighted avg

avinashelite — Tue, 08 Mar 2016 11:30:35 GMT

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

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

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 11:36:48 GMT

it need to consider both dimension

place and ship(usi)

Re: how to write dynamic expression for weighted avg

avinashelite — Tue, 08 Mar 2016 11:39:41 GMT

then remove the aggr its not required by default qlikview will consider both the dimension for the expression calculation

Re: how to write dynamic expression for weighted avg

brijesh1991 — Tue, 08 Mar 2016 11:58:02 GMT

sum( quantity * tm)/sum( total quantity )

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 12:23:54 GMT

i was trying to hide and sho w the expression but its not working ,can u help me please

i set variable in the textbox:vSelect=1

same i wrote under expression

in condition box: vSelect=1

so it mean when ever i click textbox then only this expression has to show ...if i click

Clear ALL button then it should hide .......only dimensions has to be shown

but its not happening in my case ..........can u say y

Re: how to write dynamic expression for weighted avg

kunkumnaveen — Tue, 08 Mar 2016 12:25:16 GMT

i was trying to hide and show the expression but its not working ,can u help me please

i set variable in the textbox:vSelect=1

same i wrote under expression

in condition box: vSelect=1

so it mean when ever i click textbox then only this expression has to show ...if i click

Clear ALL button then it should hide .......only dimensions has to be shown

but its not happening in my case ..........can u say y

Re: how to write dynamic expression for weighted avg

avinashelite — Tue, 08 Mar 2016 12:26:34 GMT

clear button will not clear the variable value you need to set it ...

If you want to hide the expression in clear than add a button manually and add action to clear selection and one more action to set the vSelect=0

Re: how to write dynamic expression for weighted avg

Anonymous — Tue, 08 Mar 2016 12:29:23 GMT

sum( quantity * tm)/sum( total quantity )