topic Average Time by Distinct Group in QlikView

Average Time by Distinct Group

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Hopefully this is in the right place, and if anyone can help it would be really appreciated.

I have data similar to the below table. I want to find the average time taken per distinct order and test group.

Order	Test Group	Order & Test Group	Code	Time Taken
1	A	1A	1	5
1	A	1A	2	5
1	A	1A	3	5
1	B	1B	1	15
2	A	2A	1	10
2	B	2B	1	10
3	A	3A	1	20

So in this example the code columns means that I am getting 3 counts of '1A', but I only want 1, so I want my average to be

(5+15+10+10+20)/5=12, not (5+5+5+15+10+10+20)/7=10. I basically want to ignore the duplicate 1A Order& Test Groups.

Do you know if this is possible.

Let me know if you would need any extra information.

Thank you,

Neil

Re: Average Time by Distinct Group

sunny_talwar — Thu, 03 Aug 2017 15:19:09 GMT

May be this

Avg(Aggr(Only([Time Taken]), Order, [Test Group]))

Re: Average Time by Distinct Group

effinty2112 — Thu, 03 Aug 2017 15:42:40 GMT

Hi Neil,

try:

=Sum(Aggr(Sum([Time Taken]),Order,[Test Group]))

Sum(Aggr(Count([Time Taken]),Order,[Test Group]))

In working out averages I always like to do the simple thing and that's to work out a grand total then divide by the number of instances we're taking our average over.

I think (for me at least) it's very easy to go astray when you wrap an aggr with an avg().

cheers

Andrew

Re: Average Time by Distinct Group

sunny_talwar — Thu, 03 Aug 2017 15:44:25 GMT

What issues do you foresee using Avg(Aggr())

Re: Average Time by Distinct Group

effinty2112 — Thu, 03 Aug 2017 15:56:44 GMT

Hi Sunny,

Apologies to you and Neil. I didn't read the second half of Neil's question properly, so Neil please disregard my half baked reply to you. Regarding my issues with Avg(Aggr()) I guess I'm just more comfortable writing it out the way I do and find it easier to read.

Cheers

Andrew

Re: Average Time by Distinct Group

sunny_talwar — Thu, 03 Aug 2017 16:21:56 GMT

Hey Andrew -

Not sure what you are apologizing for. You don't have to apologize brother. I guess it might be easier to read, but if you can get the result using a single Aggr(), wouldn't you want to do that instead of getting the same result using 2 Aggr()? (Unless you have a small dataset)

Re: Average Time by Distinct Group

effinty2112 — Thu, 03 Aug 2017 16:31:30 GMT

Hi Sunny,

My apologies were really because I didn't read the question properly and the answer I gave was wrong. It gave 12 10 instead of 10 12 as the result.

Following my logic the correct (though cumbersome) expression would be

Sum(Aggr(only([Time Taken]),Order,[Test Group]))

Sum(Aggr(Count(DISTINCT [Time Taken]),Order,[Test Group]))

Anyway you are right - especially with large volumes. I think I've got a hangup I need to lose, I must have had a bad experience with avg(aggr()) in the past .

Cheers

Andrew

Re: Average Time by Distinct Group

effinty2112 — Thu, 03 Aug 2017 16:39:14 GMT

Hi Sunny,

It may be a half remembered reading of this that causes by aversion to avg(aggr()).

Average – Which average?

Cheers

Andrew

Re: Average Time by Distinct Group

Anonymous — Thu, 03 Aug 2017 19:31:58 GMT

Thanks for your help guys they both work really well. Much appreciated.

Re: Average Time by Distinct Group

MarcoWedel — Thu, 03 Aug 2017 23:47:02 GMT

Hi,

another way to get this average might be:

hope this helps

regards

Marco

Re: Average Time by Distinct Group

Fri, 04 Aug 2017 06:10:50 GMT

Re: Average Time by Distinct Group

Fri, 04 Aug 2017 06:14:35 GMT

For getting Total avg, the code is : avg(aggr(avg([time taken]),order,[test group]))