case when not blank

I'm trying to create a  formula where 3 fields sfuser1, sfuser2, and sfuser3 are not equal to blank to include the users from those fields

 

So we want to include the data if either sfuser1, sfuser2, or sfuser 3 contains a user name

 

thanks

Best Answers

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    The parentheses were in the wrong place, in this particular case there is no need for parentheses. Yes you would just filter to greater than 0 in this case since your options are just 0 and 1. You can make your 1 and 0 anything you would like, if you would rather use 'true' and 'false' you could do that too, whichever makes more sense to you.

     

    If you are doing a grouped by on the bar you will want to create another beastmode and use it in the grouped by. Something like ...

    case 
    when sfuser1 is not null then 'sfuser1'
    when sfuser2 is not null then 'sfuser2'
    when sfuser3 is not null then 'sfuser3'
    end

    This will bucket them first in sfuser1 then sfuser2 then sfuser3


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • meadow_ryan
    meadow_ryan Member
    Answer ✓

    ok, so its pulling in data but just not all teh service failures for each user as its only brining in 5 for my summary number is there anyway for me to share my card with you as what I'm trying to show is we have a table called orders and each order there is a place where an sfuser1(user) and orderservicefailure1(service failure code) can be entered, an sfuser2 and orderservicefailure2, and an sfuser3, and orderservicefailure3, what I'm trying to display is a grouped bar for each sfuser the orders which contain one of the orderservicefailure codes either in orderservicefailure1 or orderservicefailure2, or orderservicefailiure3

Answers

  • Hello,

     

    You should be able to build a beastmode calculation for this then use the beastmode to filter the card.

     

    Case when sfuser1 is not null or sfuser2 is not null or sfuser3 is not null then 1 else 0 end

     

    Hope this helps!

     

    Brian

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • so I wrote it like the following and its not accepting it?

     

    CASE (when `sfuser1` is not null  
                or `sfuser2` is not null
                or `sfuser3` is not null
                
    then 1
    else 0)
    END

  • so I got the formula to calculate , but when adding to the filter its not asking to filter off of 1 or 0 just to put in a value greater than, less than, etc.

     

     

    Also, I'm doing a grouped bar and for the category on the x axis I would like to show if the users are in sfuser 1 or sfuser2, or sfuser3, is there a way to do this?

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    The parentheses were in the wrong place, in this particular case there is no need for parentheses. Yes you would just filter to greater than 0 in this case since your options are just 0 and 1. You can make your 1 and 0 anything you would like, if you would rather use 'true' and 'false' you could do that too, whichever makes more sense to you.

     

    If you are doing a grouped by on the bar you will want to create another beastmode and use it in the grouped by. Something like ...

    case 
    when sfuser1 is not null then 'sfuser1'
    when sfuser2 is not null then 'sfuser2'
    when sfuser3 is not null then 'sfuser3'
    end

    This will bucket them first in sfuser1 then sfuser2 then sfuser3


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • Hi, meadow_ryan,

     

    That's happening because Domo is calculating that beast mode as a number (1 or 0). To force Domo to see it as a text dimension, you could use 'True'/'False' or '1'/'0' (single quotes designating strings rather than numbers) in your beastmode formular rather than 1/0. Or you could use the concatenate function, wrapping your case statement like this:

    concat(case when ... then ... else ... end)

     

  • so would this be then what I put on the category instead of just sfuser1, to have when the user is in sfuser1 or sfuser2, or sfuser3 use

     

    case 
    when sfuser1 is not null then 'sfuser1'
    when sfuser2 is not null then 'sfuser2'
    when sfuser3 is not null then 'sfuser3'
    end
  • can you not make your summary number on your card the count of one of the case statements? As I believe I would want the summary to be the count of what is below, as along with the 3 different sfuser's they can record 3 different service failure types

     

    CASE when `ORDER SERVICE FAILURE` IN ('DE','FC','FF','FN','IT','NC','RCN','TN')  
                or `ORDER SERVICE FAILURE 2` IN ('DE','FC','FF','FN','IT','NC','RCN','TN')
                or `ORDER SERVICE FAILURE 3` IN ('DE','FC','FF','FN','IT','NC','RCN','TN')
                
    then 1
    else 0
    END

  • You can indeed put a beast mode in the summary number. To accomplish that, you need to explicitly aggregate in the beastmode (that is, count, sum, etc in the definition of the beastmode).

     

    Something like this:

    sum(
    case
    when ... or ... or ... then 1
    else 0
    end
    )
  • Correct this would go in the "Series" or "Category" section of the chart depending on how you wanted to view the data


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • meadow_ryan
    meadow_ryan Member
    Answer ✓

    ok, so its pulling in data but just not all teh service failures for each user as its only brining in 5 for my summary number is there anyway for me to share my card with you as what I'm trying to show is we have a table called orders and each order there is a place where an sfuser1(user) and orderservicefailure1(service failure code) can be entered, an sfuser2 and orderservicefailure2, and an sfuser3, and orderservicefailure3, what I'm trying to display is a grouped bar for each sfuser the orders which contain one of the orderservicefailure codes either in orderservicefailure1 or orderservicefailure2, or orderservicefailiure3

  • I don't believe you can share cards but if you can attach a csv with how the data looks that would probably help.


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • alternatively if you are more comfortable with excel charts create the chart in excel, attach a screenshot, and I can see if it is possible to recreate in Domo


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
This discussion has been closed.