How to Calculate Averages Across Two Different Columns

pstrauss

I'm trying to get the average duration of our phone calls and chats. For rows that have a Unique Call ID, we want to use Call Duration, for rows that have a Unique Chat ID, we want to use Chat Duration. There may be multiple records with the same Chat ID or Call ID, so I've been using FIXED BY to ensure we're using unique IDs. However when I run the following Beast Mode query, I get an error saying "An invalid column was specified in your request (the column does not exist on the DataSet). "

AVG(case

when `Chat ID` is not null

then AVG(`Chat Duration`) FIXED (BY `Chat ID`)

when `Call ID` is not null

then AVG(`Call Duration`) FIXED (BY `Call ID`)

end
)

Any ideas what I might be doing wrong? The FIXED BY statements work fine when they're not in a CASE WHEN structure.

MichelleH

You may be running into issues because of aggregation within a case statement. You can also try the formula below to bypass the case statement.

avg(AVG(ifnull(`Chat Duration`,`Call Duration`)) FIXED (BY ifnull(`Chat ID`,`Call ID`)))

jessdoe

Try

case

when `Chat ID` is not null

then avg(AVG(`Chat Duration`) FIXED (BY `Chat ID`))

when `Call ID` is not null

then avg(AVG(`Call Duration`) FIXED (BY `Call ID`))

end


MichelleH

You may be running into issues because of aggregation within a case statement. You can also try the formula below to bypass the case statement.

avg(AVG(ifnull(`Chat Duration`,`Call Duration`)) FIXED (BY ifnull(`Chat ID`,`Call ID`)))

pstrauss

Thanks for the speedy response, @MichelleH - that worked like a charm!