Calculated Fields in Cards

user068414
user068414 Member
in Magic ETL

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?

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
)

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user068414 

     

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user068414
    user068414 Member

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)
)

     

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user068414
    user068414 Member

    Thank you so much! This worked perfectly!!

Categories