Filter Line without Column Turning Blank

_DanielB_

Hi All,

I'm using a calculation with a variable (see below) to select designed time periods and need to remove my revenue line (Hierarchy = Revenue) however, when I add filter "Hierarchy Not In 'REVENUE'" the column using the below formula returns BLANK for every row in that column but does not cause any of the other values to change or become incorrect.

I tried adding an "THEN 0 ELSE" segment before the / but it yielded the same result. I'm certain it has something to do with the post / line which explicitly says = 'Revenue' but I'm not sure how to resolve this

Any Ideas?

SUM(

            CASE

                WHEN `Date_RangeSelection` = 'Year To Date' AND YEAR(`Month_StartDate`) = YEAR(CURDATE()) AND Structure <> 'Revenue' THEN `Value (€)`

                WHEN `Date_RangeSelection` = 'This Quarter' AND QUARTER(`Month_StartDate`) = QUARTER(CURDATE()) AND YEAR(`Month_StartDate`) = YEAR(CURDATE()) AND Structure <> 'Revenue' THEN `Value (€)`

                WHEN `Date_RangeSelection` = 'Prev Quarter' AND QUARTER(`Month_StartDate`) = QUARTER(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND YEAR(`Month_StartDate`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND Structure <> 'Revenue' THEN `Value (€)`

                WHEN `Date_RangeSelection` = 'Prev Month' AND MONTH(`Month_StartDate`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(`Month_StartDate`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND Structure <> 'Revenue' THEN `Value (€)`

                WHEN `Date_RangeSelection` = 'Prev 6 Months' AND `Month_StartDate` >= DATE_SUB(CURDATE(), INTERVAL 7 MONTH) AND Structure <> 'Revenue' THEN `Value (€)`

                ELSE 0

            END

  )     /

         SUM( SUM(

            CASE

                WHEN Structure = 'Revenue' AND `Date_RangeSelection` = 'Year To Date' AND YEAR(`Month_StartDate`) = YEAR(CURDATE()) THEN `Value (€)`

                WHEN Structure = 'Revenue' AND `Date_RangeSelection` = 'This Quarter' AND QUARTER(`Month_StartDate`) = QUARTER(CURDATE()) AND YEAR(`Month_StartDate`) = YEAR(CURDATE()) THEN `Value (€)`

                WHEN Structure = 'Revenue' AND `Date_RangeSelection` = 'Prev Quarter' AND QUARTER(`Month_StartDate`) = QUARTER(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND YEAR(`Month_StartDate`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) THEN `Value (€)`

                WHEN Structure = 'Revenue' AND `Date_RangeSelection` = 'Prev Month' AND MONTH(`Month_StartDate`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(`Month_StartDate`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))  THEN `Value (€)`

                WHEN Structure = 'Revenue' AND `Date_RangeSelection` = 'Prev 6 Months' AND `Month_StartDate` >= DATE_SUB(CURDATE(), INTERVAL 7 MONTH) THEN `Value (€)`

                ELSE 0

            END

        ) FIXED ())*-1

Thanks

Dan

DavidChurchman

When you say you added a filter that "Hierarchy not in REVENUE", does that mean the Structure column will never equal 'Revenue'? If so, that means that your denominator will be 0 because of your ELSE statement, and dividing by zero will result in null.