Calculated Fields in Cards

user068414

I created a calculated field in beast mode (sum of 7 columns in my dataset). I need to use this field as a summary number in cards. I am calculating total brand impressions and need to show the total as a single number for each partner and to also show the total by partner year over year. When I add the calculated field to my card I get the following error message: An issue has occurred during processing. We are unable to complete the request at this time. Can anyone help me figure out how to do this without manually adding and changing a totals column every time we get updated numbers in my webform?

GrantSmith

SUM works on a row by row basis and only takes a single parameter - the column which holds the value or a calculated value you're wishing to add together across all the rows.

 

If you're wanting the grand total of all 8 columns add them first before your SUM like this:

SUM(`Logo` + `Social Media` + `Earned Media` + `Broadcast` + `Jersey` + `Signage` + `Press Backdrop` + `Website`)

 

Now to take it a step further if any one of those columns has a NULL (missing) value it will make that entire row NULL and SUM will treat it as 0. In your case if you had Logo as NULL, Social Media as 100, Earned Media as 200 and the rest as 0 it would treat that entire row as NULL instead of 300 in your sum aggregation. To protect against NULL values in your addition chain you can tell Domo to use a default value via the COALESCE function:

COALESCE(`Logo`, 0)

 

 

This all ties into a nice bow like the following:

SUM(`Logo`,`Social Media`,`Earned Media`,`Broadcast`,`Jersey`,`Signage`,`Press Backdrop`,`Website`)

SUM(COALESCE(`Logo`, 0)
+ COALESCE(`Social Media`, 0)
+ COALESCE(`Earned Media`, 0)
+ COALESCE(`Broadcast`, 0)
+ COALESCE(`Jersey`, 0)
+ COALESCE(`Signage`, 0)
+ COALESCE(`Press Backdrop`, 0)
+ COALESCE(`Website`, 0)
)

 

GrantSmith

Hi @user068414 

 

Do you have the code for your beast mode? That will help debug your issue.

user068414

SUM(`Logo`,`Social Media`,`Earned Media`,`Broadcast`,`Jersey`,`Signage`,`Press Backdrop`,`Website`)

GrantSmith

SUM works on a row by row basis and only takes a single parameter - the column which holds the value or a calculated value you're wishing to add together across all the rows.

 

If you're wanting the grand total of all 8 columns add them first before your SUM like this:

SUM(`Logo` + `Social Media` + `Earned Media` + `Broadcast` + `Jersey` + `Signage` + `Press Backdrop` + `Website`)

 

Now to take it a step further if any one of those columns has a NULL (missing) value it will make that entire row NULL and SUM will treat it as 0. In your case if you had Logo as NULL, Social Media as 100, Earned Media as 200 and the rest as 0 it would treat that entire row as NULL instead of 300 in your sum aggregation. To protect against NULL values in your addition chain you can tell Domo to use a default value via the COALESCE function:

COALESCE(`Logo`, 0)

 

 

This all ties into a nice bow like the following:

SUM(`Logo`,`Social Media`,`Earned Media`,`Broadcast`,`Jersey`,`Signage`,`Press Backdrop`,`Website`)

SUM(COALESCE(`Logo`, 0)
+ COALESCE(`Social Media`, 0)
+ COALESCE(`Earned Media`, 0)
+ COALESCE(`Broadcast`, 0)
+ COALESCE(`Jersey`, 0)
+ COALESCE(`Signage`, 0)
+ COALESCE(`Press Backdrop`, 0)
+ COALESCE(`Website`, 0)
)

 

user068414

Thank you so much! This worked perfectly!!