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.
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).
Selecting this month for revenue.
Selecting Last 3 months for Revenue.
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