help with avg formula

[hopkinsc]

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

[sunny_talwar]

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)

[hopkinsc]

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?

[hopkinsc]

Hi Sunny (or anyone else)

any suggestions on this?

Thanks

[sunny_talwar · Accepted solution]

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

[shiveshsingh]

Hi Sunny

Please help me in understanding below sign, a bit confused

*=

[hopkinsc]

Fantastic, thanks Sunny!

[sunny_talwar]

You can read about this here

Implicit Set Operators

[hopkinsc]

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

[shiveshsingh]

Thanks