topic Re: Issue with Averages in QlikView

Issue with Averages

Wed, 25 Nov 2020 16:16:04 GMT

Hi, I'm having issues with my average calculation using time and volumes

Time Volumes

01:00:00 4

01:00:00 3

01:00:00 2

01:00:00 4

01:00:00 5

03:30:00 2

03:30:00 3

03:30:00 4

=Sum(VOLUME) / Count( [Time])

For 01:00 I would want to return an average of 3.6

For 03:30 I want to return an average of 2.75

The issue seems to be with the count section of my expression, it's counting the total value of time instead of the entries.

Re: Issue with Averages

Anil_Babu_Samineni — Fri, 18 Aug 2017 09:34:15 GMT

Just do Avg(Volumes)

Re: Issue with Averages

Fri, 18 Aug 2017 09:41:34 GMT

I've added the above expression to my pivot table but it's still not showing me the correct average.

It's seem to be trying to count the value of my time instead of the amount of entries for that specific time

Re: Issue with Averages

vinieme12 — Fri, 18 Aug 2017 09:47:58 GMT

read this post

Average – Which average?

Re: Issue with Averages

vinieme12 — Fri, 18 Aug 2017 09:49:17 GMT

try with the TOTAL keyword

either

=Sum(VOLUME) / Count( TOTAL [Time])

Sum(VOLUME) / Count( TOTAL <[Time]> [Time])

Re: Issue with Averages

Fri, 18 Aug 2017 09:52:09 GMT

Try this in front end

aggr(Avg(Volumes),Time)

Re: Issue with Averages

Anil_Babu_Samineni — Fri, 18 Aug 2017 10:08:40 GMT

Why not?

Re: Issue with Averages

masismykola — Fri, 18 Aug 2017 11:51:38 GMT

Hi in script it would be like:

temp:

LOAD * INLINE [

Time,Volumes

01:00:00,4

01:00:00,3

01:00:00,2

01:00:00,4

01:00:00,5

03:30:00,2

03:30:00,3

03:30:00,4

];

NoConcatenate

table:

load

Time,

avg(Volumes) as AvgByTime

Resident temp

Group by Time;

drop table temp;