Skip to main content
Announcements
See why Qlik was named a Leader in the 2025 Gartner® Magic Quadrant™ for Augmented Data Quality Solutions: GET THE REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III

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 ))

 2020-03-19 12_16_40-TestFSLoad - My new sheet _ Sheets - Qlik Sense.png

 

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.

Labels (2)
1 Solution

Accepted Solutions
JustinDallas
Specialist III
Author

I'm a dodo

 

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

View solution in original post

1 Reply
JustinDallas
Specialist III
Author

I'm a dodo

 

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