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
-
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'
endThis 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.1 -
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
0
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.0 -
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)
END0 -
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?
0 -
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'
endThis 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.1 -
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)
1 -
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'
end0 -
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
END0 -
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
)1 -
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.1 -
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
0 -
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.0 -
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.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive