Avg( Sum...) but only if the sum is "> 0"

[JustinDallas]

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.

[JustinDallas · Accepted solution]

I'm a dodo

AVG(AGGR(If(SUM([DOT Hours]) > 0, SUM([DOT Hours])), DriverId, LogDate ))