case when not blank

meadow_ryan

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

Property_Ninja

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

meadow_ryan

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

Property_Ninja

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.

meadow_ryan

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

meadow_ryan

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

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

Unknown

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)

 

meadow_ryan

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

meadow_ryan

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

Unknown

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
)

Property_Ninja

Correct this would go in the "Series" or "Category" section of the chart depending on how you wanted to view the data

meadow_ryan

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

Property_Ninja

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

Property_Ninja

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