Best Of
Re: Cant edit the ETL, beside I'm the owner
@camila What user role do you have? If you are not an admin, then I suggest reaching out to somebody who is an admin for your instance to verify your permissions. If you are an admin, then you should reach out to Domo suppor to troubleshoot.
Re: Teams webhook on alerts
I had to do something similar to @GrantSmith but my endpoint was Microsoft Teams. The webhook body that Domo sends is very basic:
{
"name": "{alertTitle}",
"id": "0",
"alertId": "0",
"message": "{alertMessage}",
"timestamp": "00000000"
}
Microsoft Teams (or more specifically their Adaptive Card format) requires additional information and a different syntax (the docs).
I used Webhook Relay to intercept, manipulate, and forward the data. I listed the basic steps here.
Now in our Microsoft Teams channel, Domo card alerts get pushed.
Re: Why is a field not allowing me to exclude it?
@nshively When you you join your Groups to your original dataset, if an Employee from your original dataset is not in your Groups dataset, then Domo (SQL) will make it a NULL. So your values are actually NULL not blank, and filtering with NULLS doesn't really work unless you specifially filter on IS NULL or IS NOT NULL. This wikipedia entry has a good explanation of why this is:
Now it is interesting that your IFNULL formula isn't working because that would be the solution to make it so they are not null. Would you mind posting the beast mode you're using for that? Also, since IFNULL is giving you trouble, you could use a beast mode like this:
case when ‘Group’ IS NULL then ‘Other’ else ‘Group’ end
And then just filter out Other.
Re: Defining Custom Color Palettes
We have custom palettes that can affect your entire instance. This is an admin function. Talk to your sales rep to get this enabled.
We are working on scoping additional color options soon.
Now is a good time to add to your wish list for this feature. We are definitely looking at gradients between a start and end color for all chart types. We have a few ideas on adding to the palette picker but would love to hear your preferences.
GaryGibb
Re: How to remove duplicates without affecting the blank cells?
@Vishz14 I would suggest creating two separate branches of your MagicETL: one filtered to rows where Transaction ID is null, and the other where Transaction ID is not null. You can use Remove Duplicates tile on the branch that contains Transaction ID, then merge it back with the rows without a Transaction ID using an Append Rows tile.
Re: Beastmode not recognising exisisting column
@NateBI I'm glad it worked! Would you please accept my answer as a solution to help others find the solution more quickly?
Re: Beastmode not recognising exisisting column
@NateBI Does it still return an error if you put single quotes around TRUE? It may be reading that as a field name instead of a value.
DP22 - Using Beast Mode to Build Data Storytelling - Formatted Time Duration
This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.
Use Case
We wanted a way to display how long an invoice took to process in a more human readable format. Instead of displaying the duration in seconds or hours and having the user do the “mental math” to convert it themselves this beast mode does that for the user to allow them to focus more on the story the data was telling.
Beast Mode
CONCAT( CASE WHEN `Ship Time` <= `Order Time` THEN '-' ELSE '' END, -- Days FLOOR ( ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400 ), 'd ', -- Hours, Minutes and Seconds SEC_TO_TIME ( ABS ( MOD ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`), 86400 ) ) ) )
Beast Mode Breakdown
CASE WHEN `Ship Time` <= `Order Time` THEN '-' ELSE '' END
If the ship time is before the order time precede the result with a negative. This allows us to simplify the logic calculating the difference and to have the negative displayed at the beginning of the number days instead of in the middle at the beginning of the hours.
UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`)
UNIX_TIMESTAMP returns the number of seconds (epoch) since January 1st 1970
Subtracting two UNIX_TIMESTAMP values calculates the difference between two timestamps in seconds
ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400
ABS will return the absolute value of the difference between the two times. This will force a negative difference to be positive. Again this allows us to simplify the logic and display the negative symbol one time instead of before the days and before the hours:minutes:seconds.
Dividing the number of seconds by 86400 (number of seconds in a day / 24*60*60) gets the number of days
FLOOR ( ABS ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`) ) / 86400 )
FLOOR drops anything after the decimal point returning whole days in the duration. We’ll be converting the partial days in the next step to hours, minutes and seconds.
SEC_TO_TIME ( ABS ( MOD ( UNIX_TIMESTAMP(`Ship Time`) - UNIX_TIMESTAMP(`Order Time`), 86400 ) ) )
MOD function will divide the duration in seconds by the number of seconds in a day (86400) and return the remainder. This will be the value of the partial day in seconds.
SEC_TO_TIME will take the remaining seconds and format it as HH:MM:SS
CONCAT(…)
CONCAT will add the different string values together to form a single value
Final Result:
