Best Of
Re: Combining Filtered Rows to one table
Hi @SamanthaC,
Yes, this can be accomplished using a CASE statement in Beast Mode. However, the results will be displayed in a column rather than rows. If you provide additional details or a sample dataset, I’d be happy to assist further.
Re: Combining Filtered Rows to one table
Agree with Manasi, you could use a CASE statement in a BeastMode. Something like:
CASE
WHEN [conditions for first table] THEN 'Row 1'
WHEN [conditions for second table] THEN 'Row 2'
WHEN [conditions for third table] THEN 'Row 3'
ELSE 'Exclude'
END
Then filter your card to exclude the 'Exclude' rows.
New features coming to Domo next week!
Many of you may have noticed in Domo starting yesterday the in product preview of what is coming to Domo next week as part of our always exciting Domopalooza March release (how is it already here.). I am sharing the attached one pager which includes a summary of what is coming and reflects what you saw in product (sorry, still some alpha and coming soon sneaks that we are waiting to share on the main stage next week). I will post an update next week with all the updates.
We are excited to get these new features out to you all and cannot wait to see the problems you will solve with them!
Re: Previous Month Last Year value Formula
Gotcha. I would recommend using a LAG function
- Structure your data to look something like this:
Month | Amount |
---|---|
01/01/2024 | 100 |
02/01/2024 | 103 |
03/01/2024 | 105 |
04/01/2024 | 110 |
05/01/2024 | 115 |
06/01/2024 | 116 |
07/01/2024 | 120 |
08/01/2024 | 126 |
09/01/2024 | 129 |
10/01/2024 | 135 |
11/01/2024 | 139 |
12/01/2024 | 120 |
01/01/2025 | 140 |
02/01/2025 | 146 |
03/01/2025 | 149 |
2. Use LAG to get the previous year value as a new field, `1YearPreviousAmount`
Month | Amount | 1YearPreviousAmount |
---|---|---|
01/01/2024 | 100 | |
02/01/2024 | 103 | |
03/01/2024 |
| |
04/01/2024 | 110 | |
05/01/2024 | 115 | |
06/01/2024 | 116 | |
07/01/2024 | 120 | |
08/01/2024 | 126 | |
09/01/2024 | 129 | |
10/01/2024 | 135 | |
11/01/2024 | 139 | |
12/01/2024 | 120 | |
01/01/2025 | 140 | 100 |
02/01/2025 | 146 | 103 |
03/01/2025 | 149 |
|
More info here:
scroll down to the "Offset Functions" sectionRe: REGEX Tutorial to Validate US Phone Numbers
@Jonathan53891 This is awesome! I'll definitely be using this in the future
REGEX Tutorial to Validate US Phone Numbers
Hi everyone,
I wanted to create this discussion post to share a methodology I created using Domo's Regular Expressions in Magic ETL to parse and validate US phone numbers. This validation process follows the basic standards set by the North American Numbering Plan (NANP) and helps identify valid US phone numbers from a dataset using just a small reference table and just a few ETL tiles and formulas.
Valid US Phone Number Parsing Tutorial:
- Create a US Area Codes reference table: Simply upload the attached spreadsheet (or copy and paste the data into a Domo webform) that contains a current list of valid US area codes.
- Add a Formula tile and create an Area Code field using the following formula: LEFT(TRIM(REGEXP_REPLACE(`Phone Number`, '[^0-9]', '')),3)
- Join the US Area Codes reference table to your main dataset that using the Area Code as the join key. To resolve the duplicate field names, simply rename the reference table field to Valid Area Code.
- In another Formula tile, create a Valid Area Code? flag using the following formula: IFNULL(`Valid Area Code`,'Invalid')
- Create another formula called Valid Phone Number? underneath the "Valid Area Code?" flag:
CASE
WHEN `Valid Area Code?` = 'Invalid' THEN 'FALSE'
WHEN REGEXP_LIKE(TRIM(REGEXP_REPLACE(`Phone Number`, '[^0-9]', '')), '^(?:[2-9][0-9]{2}){2}[0-9]{4}$')
AND NOT REGEXP_LIKE(TRIM(REGEXP_REPLACE(`Phone Number`, '[^0-9]', '')), '^(.)\1{9}$')
AND NOT REGEXP_LIKE(TRIM(REGEXP_REPLACE(`Phone Number`, '[^0-9]', '')), '^[0-9]{3}911[0-9]{4}$')
AND NOT REGEXP_LIKE(TRIM(REGEXP_REPLACE(`Phone Number`, '[^0-9]', '')), '^[2-9][0-9]{2}555[0-9]{4}$')
THEN 'TRUE'
ELSE 'FALSE'
END
The formula above checks the following information for each phone number:
- The number contains exactly 10 numerical digits with a valid structure defined by the North American Numbering Plan (NANP): (NPA) NXX-XXXX.
- The number's area code (NPA) and prefix (NXX) both start with digits 2-9.
- The number is not composed entirely of the same digit (e.g., 1111111111).
- The number does not contain the "911" sequence in the prefix (NXX) position.
- The number does not contain the "555" sequence in the prefix (NXX) position.
I hope this is helpful for anyone working with large datasets of phone numbers who need to filter out invalid US phone numbers! Please feel free to provide any feedback to further optimize or expand on this logic, especially if anyone has patterns for international formats or any additional edge cases I may have missed.
Thank you!
Re: How do I add a Field to the 'Card Title' or 'Description' even if it is not a card filter?
@brycec LISTAGG is a new one for me, thank you! I played with it, and it also works with DISTINCT, so I'll add that to your suggestion:
REPLACE(LISTAGG(DISTINCT `Current Pages), ',', ', ')
Re: How do I add a Field to the 'Card Title' or 'Description' even if it is not a card filter?
Oh, great point @DavidChurchman! Even better, there is a way to add all those Current Pages to the Summary Number. Simply use the LISTAGG(`Current Pages`) and you should be set! There is no way to adjust the LISTAGG separator, but you could just do REPLACE(LISTAGG(`Current Pages`), ',', ', ') to change it to have a space after the comma or you can adjust that Beast Mode accordingly to have any separator you want.

Re: Comment out tiles in MagicETL
Commenting to bump this and hope that it gets renewed interest. @AndreaHenderson now that we have sections, the ability to comment those out and/or hide them would be awesome.