Using Sum vs AVG for period Comparison

ColinHaze
ColinHaze Member
in Beast Mode

Hi,

The column I am trying to fix is 'Current Month'. The goal is to use a variable to switch between different metrics. When I use Sum in front of my case statements I achieve the current months number, however I need the averages for the %s. When I switch it to avgs the number gets cut in half every time I switch the time frame( Example: Last 3 Months, Last 4 months, etc…)

The correct number for this row should be 54%, which I get when I select 'This Month' in the top right.

image.png

However as soon as I select any other time frame it gets cut in half? This doesnt happen to my revenue (which is using a SUM in front of the case statement).

image.png

Selecting this month for revenue.

image.png

Selecting Last 3 months for Revenue.

image.png

Why Might this be the case?

CASE WHEN Comparison Type = 'Revenue' THEN
(SUM(CASE WHEN LAST_DAY(Fiscal Months) = LAST_DAY(CURDATE()) THEN Revenue ELSE 0 END))
WHEN Comparison Type = 'Driving%' THEN
Avg(CASE WHEN LAST_DAY(Fiscal Months) = LAST_DAY(CURDATE()) THEN (Driving%) ELSE 0 END)
WHEN Comparison Type = 'Empty%' THEN
Avg(CASE WHEN LAST_DAY(Fiscal Months) = LAST_DAY(CURDATE()) THEN Emptypercent ELSE 0 END)
END

Answers

  • JasonAltenburg
    JasonAltenburg Coach

    Can you provide more clarity as to what the Driving% column is (is it a calculated field with an existing aggregation, or is it a pre-calculated number?) and provide a sample of what the data structure is underlying for a couple of rows?

  • ColinHaze
    ColinHaze Member

    JasonAltenburg

    I figured out a solution, I basically had to sum the column then divide the column to get the results to be static for that month.

    Solution is as follows


    IFNULL(CASE
    WHEN Comparison Type = 'Revenue' THEN
    ROUND(SUM(CASE
    WHEN LAST_DAY(Fiscal Months) = LAST_DAY(CURDATE()) THEN Revenue
    ELSE 0
    END), 0)

    WHEN `Comparison Type` = 'Driving%' THEN
    
    ROUND(
    
        SUM(CASE 
    
            WHEN LAST_DAY(`Fiscal Months`) = LAST_DAY(CURDATE()) THEN `Driving%` 
    
            ELSE 0 
    
        END) 
    
        / COUNT(CASE 
    
            WHEN LAST_DAY(`Fiscal Months`) = LAST_DAY(CURDATE()) 
    
            THEN `DriverName` 
    
        END) * 100, 2
    
    )

    
WHEN `Comparison Type` = 'Empty%' THEN
    
    ROUND(
    
        SUM(CASE 
    
            WHEN LAST_DAY(`Fiscal Months`) = LAST_DAY(CURDATE()) THEN `Emptypercent` 
    
            ELSE 0 
    
        END) 
    
        / COUNT(CASE 
    
            WHEN LAST_DAY(`Fiscal Months`) = LAST_DAY(CURDATE()) 
    
            THEN `DriverName` 
    
        END) * 100, 2
    
    )

    END,0)

Categories