9 Replies Latest reply on Sep 5, 2013 9:19 AM by Joshua Milligan

Average Count

sinadashtipour Sep 5, 2013 4:29 AM

Hello,

I have seen lots of discussions around this topic but havent been able to find anything clear. Please see my attached workbook.

I simply want to be get the average number Jobs that has been run per Service. And clearly the using the built in AVG here wont work.

Thanks in advance.

AvgPerService.twbx.zip 24.5 KB

1. Re: Average Count

Matt Lutton Sep 5, 2013 5:54 AM (in response to sinadashtipour)
You can do this, but when I look at your workbook, I'm not certain what you want the result to be. Can you give me an example of the results you want?

The attached gives you the average across all ServiceNames

AvgPerService ML.twbx.zip 25.0 KB
Like Show 0 Likes(0)

Actions
2. Re: Average Count

Joshua Milligan Sep 5, 2013 5:51 AM (in response to sinadashtipour)
Sina,

How about using the Window_Avg() funciton? I've attached a workbook with some inline documentation to show how it might work for you.

Regards,
Joshua

AvgPerService.twbx.zip 16.9 KB
Like Show 0 Likes(0)

Actions
3. Re: Average Count

Matt Lutton Sep 5, 2013 5:55 AM (in response to Joshua Milligan)

Joshua's workbook does a much better job of utilizing and explaining the principles here.
Like Show 0 Likes(0)

Actions
4. Re: Average Count

sinadashtipour Sep 5, 2013 6:35 AM (in response to Joshua Milligan)
Hi Joshua,

Thanks for documentation. However I think I didn't explain myself clearly on what I'm trying to achieve.

I need to get the average for the Funds at Service Level. So it should look something like this:

France    4.1
Germany 4
UK    12.3
USA   1

AvgPerService.twbx.zip 23.8 KB
Like Show 0 Likes(0)

Actions
5. Re: Average Count

Joshua Milligan Sep 5, 2013 7:16 AM (in response to Matt Lutton)

Thanks Matthew! I don't know how many times I've hit reply and then by the time I've got everything in place and post it, I realize someone else has already answered!

Regards,
Joshua
Like Show 0 Likes(0)

Actions
6. Re: Re: Average Count

Joshua Milligan Sep 5, 2013 7:17 AM (in response to sinadashtipour)
Sina,

Ah! Well that is a little different, but not much. You can still use Window_Avg (in fact, it's the exact same calculation) -- it's just a different scope for the calculation. Here's the workbook to demonstrate.

Regards,
Joshua

AvgPerService.twbx.zip 19.6 KB
Like Show 0 Likes(0)

Actions
7. Re: Average Count

Matt Lutton Sep 5, 2013 7:18 AM (in response to Joshua Milligan)

Actually, I had replied, then added the file a couple minutes later. So, you may have posted your workbook a few seconds before me. And also, yours is better
Like Show 0 Likes(0)

Actions
8. Re: Average Count

sinadashtipour Sep 5, 2013 9:17 AM (in response to Joshua Milligan)

Thanks that worked very well. Although I soon after realised that I could have also done this calucation:
count([JobID])/countd([Fund])
:-)
Like Show 0 Likes(0)

Actions
9. Re: Average Count

Joshua Milligan Sep 5, 2013 9:19 AM (in response to sinadashtipour)

Sina,

You are welcome! You are right -- that calculation is much simpler too. You just have to be aware of the level of aggregation in your view when you use it.

Regards,
Joshua
Like Show 0 Likes(0)

Actions