COMPLEX AVERAGE

[philgood34]

Hi everyboy

my challenge today :

I have these table (productivity analysis) with    VHT = CATTC / HEURES

I made TOTAL weighted average for VHT like this (thank's Sunny Talwar !) :

If(Dimensionality() = 0, Sum(VMS_CATTC)/Sum(Aggr(Sum(VMS_CATTC)/Sum(CIB_VHT), NCL_LIB)), Sum(CIB_VHT))

Correct !

Now is my issue :

i reload my table with some sectors who do'nt produce CATTC but with a productivity calculated with CATTC TOTAL

it does not match here :-((

So i want HEURES  to be calculated by 117127322/VHT if VHT <>0 , else HEURES will be calculated by CATTC/VHT

Obviously if VHT = 0, i don't want any calcul ... (to avoid DIV/0)

Crazy isn't it ?

Hope anyone 'll unterstand my issue

regards

[philgood34]

i wanted to say, i'll try tu find the expression of VHT for the total row.

no succes as you can see below

[sunny_talwar]

What number are you looking to get for VHT Total?

[philgood34]

now i want to calculate the weighted average for VHT TOTAL

294 expected (117127322/398105)  instead of 529 with the wrong expression

you have to know that the VHT in the column below are datas no calculated

i look for the right expression for the weighted average for VHT TOTAL

[sunny_talwar]

May be this

If(Dimensionality() = 0, Sum(TOTAL VMS_CATTC)/Sum(TOTAL Aggr(If(Sum(VMS_CATTC) = 0, Sum(TOTAL VMS_CATTC)/Sum(CIB_VHT), Sum(VMS_CATTC)/Sum(CIB_VHT)), NCL_LIB)), Sum(CIB_VHT))

[sunny_talwar]

or this

If(Dimensionality() = 0, Sum(VMS_CATTC)/Sum(Aggr(If(Sum(VMS_CATTC) = 0, Sum(TOTAL VMS_CATTC)/Sum(CIB_VHT), Sum(VMS_CATTC)/Sum(CIB_VHT)), NCL_LIB)), Sum(CIB_VHT))

[philgood34]

got it

If(Dimensionality() = 0, Sum(VMS_CATTC)/Sum(Aggr(If(Sum(VMS_CATTC) = 0, Sum(TOTAL VMS_CATTC)/Sum(CIB_VHT), Sum(VMS_CATTC)/Sum(CIB_VHT)), NCL_LIB)) ,Sum(CIB_VHT))

just a syntax error ...

sorry

thank's again

[sunny_talwar · Accepted solution]

Awesome, please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[sunny_talwar]

philgood34‌ I request you to appropriately mark the correct response which helped you get your requirement. The goal of this exercise is to make sure that future visitors to this page can easily find correct and helpful responses.

Thank you,

Sunny

[philgood34]

sorry i did'nt understood

is it still time to do this exercise ? i think that the question is close

now ?

2017-08-11 19:04 GMT+02:00 Sunny Talwar <qcwebmaster@qlikview.com>:

[sunny_talwar]

You did close the thread, but you marked my request to close the discussion as the correct response. If a user who have similar problem comes to this thread for answers, he is going to see this as the correct response (and helpful as well)

Would that help him? no, right? I am requesting you to select a response which will truly help the future visitors (and mark helpful things which you think are going to be truly helpful for others)

Best,

Sunny