topic Re: COMPLEX AVERAGE in QlikView

COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 15:16:32 GMT

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

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 15:23:53 GMT

What is this number here? 117127322

Is that a constant or is that coming from a calculation?

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 15:41:41 GMT

it'is the sum of the CATTC (here the sum of selectionned sectors beauté santé, liquides, epicerie , entretien) :

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 15:44:50 GMT

So based on your screenshot above, what are the expected value for the HEURES column?

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:01:53 GMT

398105 expected

here is the workbook

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:09:46 GMT

May be like this

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

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:15:45 GMT

SO GOOD ! let me try to the total vht ..

thank's a lot again !

see you master

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:17:10 GMT

let me try to the total vht ..

Not sure I understand this?

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:26:51 GMT

failed !

i tried this for the VHT TOTAL

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

but i got error message "garbage after expression

a idea ?

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:33:29 GMT

I am not sure I follow.... What are you trying to do?

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:33:50 GMT

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

no succes as you can see below

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:34:30 GMT

What number are you looking to get for VHT Total?

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:39:53 GMT

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

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:43:31 GMT

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

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:43:53 GMT

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

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 16:54:14 GMT

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

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 16:55:54 GMT

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

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 17:03:48 GMT

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

Re: COMPLEX AVERAGE

philgood34 — Fri, 11 Aug 2017 18:18:53 GMT

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

Re: COMPLEX AVERAGE

sunny_talwar — Fri, 11 Aug 2017 18:28:58 GMT

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