Average of fields not displaying correctly

[raynac]

Hey all,

I cannot figure this out!  I have added an average of time field into a table in a view I am creating but the number, while appearing, is clearly way lower than the average should be.

Per several solutions I have seen here, (where ACT is the field I am attempting to average) I am using:

=Interval( Avg( Interval#(ACT,'hh:mm:ss')), 'hh:mm:ss')

But I must be missing something.

Any help is appreciated!

Rayna

[sunny_talwar · Accepted solution]

Try this

=Interval( Avg( Interval#(Aggr(ASA, Date, Hour, ASA),'hh:mm:ss')), 'hh:mm:ss')

[raynac]

And once again, Sunny to the rescue!  Thank you so much!  I would love to understand Interval and why there are several "layers" required to get this calculation to work.  I'll find that in the user's manual.

Thank you again!

[sunny_talwar]

This is a simple one. You see 23 rows in the table box because it shows the unique combination of the listed dimension, but in reality some of them repeat. In fact the average is calculated based on a total of 34 rows

See how some of the rows have a count of more than 1? That is what was happening... you wanted Sum/23... whereas QlikView was doing Sum/34

[raynac]

Thank you so much for the explanation!  That makes sense now and begs the question of whether these "extra" data rows ought to actually be included in my calculations, after all, although it seems unlikely.  Definitely a question for my company ownership!

[sunny_talwar]

---

@raynac wrote:

Definitely a question for my company ownership!

---

Agreed 🙂