
Specialist III
2020-03-19
12:17 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Avg( Sum...) but only if the sum is "> 0"
Hello Everyone,
I'm working with data similar to below. The question I'm looking to answer is "Give me the average sum of DOT Hours per driver per day excluding days that a driver has a 0"
Drivers:
LOAD *, [DriverId] AS '%driver_key'
;
LOAD * Inline
[
'DriverId'
'Mickey Mouse',
'Goofy'
]
;
DriverLogTable:
LOAD *,
Date#(LogDateString,'M/D/YYYY') AS LogDate
;
LOAD * Inline
[
'%driver_key', 'LogDateString', 'DOT Hours',
'Mickey Mouse', 3/14/2020, 0,
'Mickey Mouse', 3/12/2020, 4.9,
'Mickey Mouse', 3/15/2020, 0,
'Mickey Mouse', 3/21/2020, 2.7,
'Mickey Mouse', 3/28/2020, 0,
'Goofy', 3/9/2020, 4.3,
'Goofy', 3/10/2020, 0,
'Goofy', 3/15/2020, 11.9,
'Goofy', 3/20/2020, 0,
'Goofy', 3/29/2020, 2.5,
]
;
DROP FIELD LogDateString
;
EXIT Script
;
As of now, I have the following Set Analysis which gives me the wrong answer because I haven't excluded the 0s.
AVG(AGGR(SUM([DOT Hours]), DriverId, LogDate ))
The number that I'm looking to get is 5.26
I've tried adding various IFs and [DOT Hours] > 0 to no avail.
Any help on this is greatly appreciated.
- Tags:
- aggr()
550 Views
1 Solution
Accepted Solutions

Specialist III
2020-03-19
03:25 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm a dodo
AVG(AGGR(If(SUM([DOT Hours]) > 0, SUM([DOT Hours])), DriverId, LogDate ))
523 Views
1 Reply

Specialist III
2020-03-19
03:25 PM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm a dodo
AVG(AGGR(If(SUM([DOT Hours]) > 0, SUM([DOT Hours])), DriverId, LogDate ))
524 Views
