topic Re: Assist with Average Calculation - exclude some values in QlikView

Assist with Average Calculation - exclude some values

QFanatic — Sat, 16 Nov 2024 00:35:02 GMT

Hi guys

Hoping this will be quick and simple, LOL!

I am calculating an average in a chart - to Average out an Hour_Volume over Weekday(WKDAY) and REQ_HOUR.

REQ_HOUR, for a normal day will contain values from 00 to 23 (count - 24 hours in the day)

Sometimes, though, my REQ_HOUR returns a null value (problem in the underlying data) - so then I have hard-coded REQ_HOUR = 'NULL'.

The problem comes in, in the Average Calc - when there are 24 REQ_HOURS - the Average statement works fine.

However, when there are 25 hours (the 'NULL' included) the Average works incorrectly (that is to be expected)

Now, in the original statement I have at the BOTTOM of this post..how do I EXCLUDE where REQ_HOUR = 'NULL' in the TOTAL statement? so that the calculation is done correctly?

Much appreciation.

avg({$<PERIOD={$(vMaxMonth)},REQ_DATE=, TYPE_GRAPH = {'HOURLY'}, REQ_HOUR -= {'NULL'}>}total <WKDAY,REQ_HOUR> HOUR_VOLUME)

Re: Assist with Average Calculation - exclude some values

agigliotti — Fri, 29 May 2020 08:21:41 GMT

Try with this one:

avg({$<PERIOD={$(vMaxMonth)},REQ_DATE=, TYPE_GRAPH = {'HOURLY'}, REQ_HOUR = {"*"} >} total <WKDAY,REQ_HOUR> HOUR_VOLUME)

Re: Assist with Average Calculation - exclude some values

QFanatic — Fri, 29 May 2020 08:46:58 GMT

Hello

I will definitely try. What does the * mean in this context?

Thank you

Re: Assist with Average Calculation - exclude some values

agigliotti — Fri, 29 May 2020 19:29:20 GMT

the * means all field values except null.