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.

_DanielB_

Aye, I'm pretty sure as I'm saying in the calculation above ( Hierarchy = 'Revenue') and then filtering out the respective line item, I end up with the problem you've mentioned

Sadly, I'm only a few months into using Domo so I haven't discovered a solution to the aforementioned problem :(

DavidChurchman

Could you give a sample of what your data looks like (anonymized) and describe the end-result you're trying to achieve? Ideally, mock up a sample of what you expect to produce from your example data.

It's difficult to trouble-shoot a big BeastMode like that without understanding what your data looks like.