Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I'm a dodo
AVG(AGGR(If(SUM([DOT Hours]) > 0, SUM([DOT Hours])), DriverId, LogDate ))
I'm a dodo
AVG(AGGR(If(SUM([DOT Hours]) > 0, SUM([DOT Hours])), DriverId, LogDate ))