How to Calculate Averages Across Two Different Columns

pstrauss
pstrauss Member
in Beast Mode

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.

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

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

Answers

  • jessdoe
    jessdoe Contributor

    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
    MichelleH Coach
    Answer ✓

    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
    pstrauss Member

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

Categories