topic Re: Average....Strange this happening in QlikView

Average....Strange this happening

Fri, 03 Feb 2017 10:28:35 GMT

I have a chart showing the total arrivals by hour of the day over the last two weeks:

Dimension = [ArrivalHour]

Expression = sum( {<.Last2Weeks={'1'}>}total_arrivals)

I need to change this to show the average arrivals per hour for the whole two week period.

However, when I change the expresion to this :

sum( {<.Last2Weeks={'1'}>}total_arrivals) / sum( total {<.Last2Weeks={'1'}>}total_arrivals)

the chart is showing less than zero per hour for the two week period :

Can anyone explain why this is happening ? The average for 12 midday should be 33.

Many thanks for any help!

TJM

Re: Average....Strange this happening

tresB — Fri, 03 Feb 2017 10:39:03 GMT

Not sure I understand your expected output. May be this:

Avg( Total {<.Last2Weeks={'1'}>}total_arrivals)

Re: Average....Strange this happening

swuehl — Fri, 03 Feb 2017 10:39:43 GMT

If you divide the absolute number per bin by the absolute total number, you get the percentage of that bin compared to the total.

In your case, approx. 6-7% of the arrivals happen around 12 midday.

An average arrival could be calculated by

sum( {<.Last2Weeks={'1'}>}total_arrivals) / Count( DISTINCT {<.Last2Weeks={'1'}>} DateFIELD)

sum( {<.Last2Weeks={'1'}>}total_arrivals) / 14 /* 14 days in period */

Re: Average....Strange this happening

sunny_talwar — Fri, 03 Feb 2017 10:39:51 GMT

Right now what you are doing is converting your total_arrival data into percentage.... basically if across the 24 hours in the day for 2 weeks you have a sum of 2000 (for example) and 0 hour is 50, then you are doing 50/2000.

Have you tried Avg() instead of Sum?

Avg({<.Last2Weeks={'1'}>} total_arrivals)

Sum({<.Last2Weeks={'1'}>} total_arrivals)/14