Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average....Strange this happening

Hi

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)

arrivals by hour_total.PNG

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 :

arrivals by hour_total_an.PNG

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

Many thanks for any help!

TJM

3 Replies
tresesco
MVP
MVP

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

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

swuehl
MVP
MVP

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)


or


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



sunny_talwar

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)

or

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