topic Re: Help with averages in QlikView

Help with averages

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

Sum(UNITS)/count(distinct(PACKAGE_ID))/sum(total units)/count(total distinct(PACKAGE_ID))

I am attempting to calculate an individiual sales reps average units per sale against teh business' overall average units per sale. This is the expression I wrote but delivers a - for all reps.

sum(units)/count(distinct(package_ID) is 100% accuare to calculate each reps personal.

Now I am trying to figure out how much percentage difference between their average units per sale vs the business as a whole(all reps).

any ideas?

Thanks in advance as always!

Re: Help with averages

CarlosAMonroy — Tue, 09 Jan 2018 20:13:54 GMT

Hi Kenneth,

I guess you are missing some brackets also.

Try this:

(Sum(UNITS)/count(distinct PACKAGE_ID)) / (sum(total UNITS)/count(total distinct PACKAGE_ID))

Hope that helps.

Carlos M

Re: Help with averages

Anonymous — Tue, 09 Jan 2018 20:22:11 GMT

Thank you.

It's the exact same result. I just get the -

Re: Help with averages

m_woolf — Tue, 09 Jan 2018 20:27:39 GMT

I notice that in the first part of your expression, UNITS is all caps. In the last part it is lower case. Are these two different fields?

Re: Help with averages

Anonymous — Tue, 09 Jan 2018 20:28:55 GMT

same field, sorry about the caps

Re: Help with averages

Anonymous — Tue, 09 Jan 2018 20:30:29 GMT

turns out that was indeed the problem, qlik validated the expression with ok but in fact did not recognize the units as UNITS.

Thanks for pointing it out.

solved.