topic Re: help with avg formula in QlikView

help with avg formula

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i have 2 charts in a QVW with a formula to get the avg score, 1 is by employee, period, dept (pivot table) and the other is a summary by dept (straight table)

if you keep the selections as is and export the pivot table to excel, the total column sums to 89.735 and averages to 3.09.

i am trying to get 3.09 into the summay staight table but i can't seem to get it to wok.

is anyone able to help please?

the summary table will still need to calculate correctly when the selections are removed and it shows all depts.

i have attached a sample.

Thanks in advance

Re: help with avg formula

sunny_talwar — Mon, 05 Mar 2018 17:42:47 GMT

Try this

=Sum(Aggr(Sum([raw_Overall Score])/

(Count(TOTAL <[dpt_Main Dept], he_UserFullName> {<[dpt_Main Dept] *= {"*"}, he_UserFullName *= {"*"}, nd_FISCAL_PERIOD *= {"*"}, [raw_Overall Score] = {">0"}>} [raw_Overall Score])), [dpt_Main Dept], he_UserFullName,[raw_Employee ID (UC)], nd_FISCAL_PERIOD))/

Count(DISTINCT {<[dpt_Main Dept] *= {"*"}, he_UserFullName *= {"*"}, nd_FISCAL_PERIOD *= {"*"}, [raw_Overall Score] = {">0"}>} he_UserFullName)

Re: help with avg formula

hopkinsc — Mon, 05 Mar 2018 17:58:50 GMT

Hi Sunny,

thanks for your help.

that works when there is a selection on 'Department'. but when its removed (so all Departments are showing), the total value is 3.18 but when exporting the data into excel, the average is 3.218 and the value that was previously against 'On Trade - Field Sales' changes to 2.95 from 3.09.

do you know whats wrong?

Re: help with avg formula

hopkinsc — Tue, 06 Mar 2018 11:50:34 GMT

Hi Sunny (or anyone else)

any suggestions on this?

Thanks

Re: help with avg formula

sunny_talwar — Tue, 06 Mar 2018 17:04:38 GMT

Try this

Sum(Aggr(Sum({<he_UserFullName *= {"*"}>}[raw_Overall Score])/

Count(TOTAL <[dpt_Main Dept], JOIN_Department, he_UserFullName>{<[raw_Overall Score] = {'>0'}>} [raw_Overall Score]), [dpt_Main Dept], JOIN_Department, he_UserFullName, nd_FISCAL_PERIOD))

/Count(Aggr(If(Sum({<he_UserFullName *= {"*"}>}[raw_Overall Score]) <> 0, he_UserFullName), [dpt_Main Dept], JOIN_Department, he_UserFullName))

Re: help with avg formula

shiveshsingh — Tue, 06 Mar 2018 17:11:29 GMT

Hi Sunny

Please help me in understanding below sign, a bit confused

Re: help with avg formula

hopkinsc — Tue, 06 Mar 2018 17:12:39 GMT

Fantastic, thanks Sunny!

Re: help with avg formula

sunny_talwar — Tue, 06 Mar 2018 17:13:17 GMT

You can read about this here

Implicit Set Operators

Re: help with avg formula

hopkinsc — Tue, 06 Mar 2018 17:18:48 GMT

*= is similar to = in set analysis.

i.e.

in set analysis, if you use

REGION={'EAST'}

then the expression will be locked to that region no matter what the selection is on REGION.

if you use..

REGION *={'EAST'}

then the expression will return the values relating to EAST if either no selections or EAST is selected, but if another selection is made on the REGION field, i.e. WEST, then the expression returns ZERO.

The link that Sunny gave will explain it better.

Re: help with avg formula

shiveshsingh — Wed, 07 Mar 2018 05:31:31 GMT

Thanks

Re: help with avg formula

shiveshsingh — Wed, 07 Mar 2018 05:31:41 GMT

Thanks