Replacing nulls when adding across columns

Reply
Highlighted
Yellow Belt

Replacing nulls when adding across columns

I have a table which contains null values, and I want to get a dynamic (i.e. it responds to filters - therefore has to be done in beast mode, as I understand) total of values across three columns, keeping null values in the orignal columns but treating them as '0's for the purpose of the calculation. I have tried several methods none of which work, my most recent attempt is:

 

(case when LENGTH(`2016`) = 0 then 0 else `2016` end)+
(case when LENGTH(`2017`) = 0 then 0 else `2017` end)+
(case when LENGTH(`2018`) = 0 then 0 else `2018` end)

Can anyone point out where I am going wrong? For context, I want to use the new column ultimately to calculate a % of grand total (of displayed data), and belive this is the first step.


Accepted Solutions
Yellow Belt

Re: Replacing nulls when adding across columns

Solution was 

(CASE WHEN `2016` IS NULL THEN '0' ELSE `2016` END)+
(CASE WHEN `2017` IS NULL THEN '0' ELSE `2017` END)+
(CASE WHEN `2018` IS NULL THEN '0' ELSE `2018` END)

I think NULL has to be capitalised, as I had tried somethign simiilar before without success.

View solution in original post


All Replies
Yellow Belt

Re: Replacing nulls when adding across columns

Solution was 

(CASE WHEN `2016` IS NULL THEN '0' ELSE `2016` END)+
(CASE WHEN `2017` IS NULL THEN '0' ELSE `2017` END)+
(CASE WHEN `2018` IS NULL THEN '0' ELSE `2018` END)

I think NULL has to be capitalised, as I had tried somethign simiilar before without success.

View solution in original post

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!