Best Of
Re: Formatting a number in CONCAT
@DomoTeam SQL has a "FORMAT(`Value`)" option in their syntax which allows for this type of fuctionality. I spoke to some people at #DP17 (Domopalooza) and they suggested putting this on Dojo. Is it possible to add the format number functionality?
Re: Formatting a number in CONCAT
@RGranada serve ?
@Rich & @Domo_Diesel any future update or improvement in this space to automate these sort of tasks predicted to be implemented soon ?
Domo IDEAs Conference - Beast Modes - Number Formatting
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to format numbers a number when concatenating with a string.
Problem:
When concatenating a string with a number the resulting value is a string and unable to utilize Domo's built in formatting.
Solution:
Check the length of the number using the LENGTH function and then get the appropriate segments of the number using SUBSTRING and then combining them all together using CONCAT
-- Author:
-- Created:
-- Last Modified:
-- Description:
-- Checks the length of a number to determine how to insert thousands separators (,) using SUBSTRING to get 3 digit pieces of the number.
CONCAT('Total Amount: ',
CASE WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 12 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 10, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 11 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 9, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 10 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 9 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 7, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 8 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 6, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 7 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 5, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 6 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 3), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 4, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 5 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 2), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 3, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) = 4 THEN
CONCAT(
SUBSTRING(ROUND(SUM(`random_number`), 0), 1, 1), ',',
SUBSTRING(ROUND(SUM(`random_number`), 0), 2, 3)
)
WHEN LENGTH(ROUND(SUM(`random_number`), 0)) <= 3 THEN
ROUND(SUM(`random_number`), 0)
WHEN SUM(`random_number`) IS NULL THEN 0
ELSE 'ERROR'
END
)
Note:
These beast modes only go to 100 Billion (12 digits). Any number higher would need to expand using the same logic for higher lengths.
Re: Restore Card Version
This is a HUGE bummer!
I'm just getting really deep with Domo and had assumed the "History" menu item meant I could restore.
Now that I need it I see that history doesn't really provide any value 😞
DHo
Re: Dataset TimeZone Select
Responding to @DataMaven….trying to explain as best I can recall. Users need to pay close attention to data structures and accuracy. If I retrieve a date, I don't want it converted to a string or a datetime value. I want a date. And I want an accurate date.
When we first configured Domo, we needed to pull from a third party API; many connectors. The data in the API had the date and times in the proper time zones. But Domo's API was assuming that the internal data was in UTC; and converting that data instead of leaving it alone. When we were validating data, we found that transactions were missing on days at the beginning or ends of our timeframe. No matter what was tried, the data summary was wrong. The only way we could get Domo to leave the data alone was to set our instance to UTC.
Re: Average question
@shreeyab The issue is because you are grouping your data by Day, so your calculation only includes orders from that particular day. Just so I understand your requirements, what should a weekly average by day represent? Is this the week to date average, rolling 7 day average, or overall average (including future dates)?
Re: Weekly chart displaying differently in child instance
Hi DataMaven,
Make sure the Week Display setting in your child instance matches the one in your parent instance (Admin > Company Settings, at the bottom of the page).
akeating
Re: Bar Graph Sorting
In the Chart Properties under General, set Sort On Totals to descending. This will make your chart group by the overall total and not the individual items.
Re: Beastmode Error - An issue has occurred during processing.
I agree with @marcel_luthi that the issue is likely caused by the nested aggregation and that it would be helpful to see a sample of the data. I'd also suggest summarizing the logic you used to create the beast mode in words so we can point you in the right direction.
Re: changing a column data type
There are a few ways to change the column type. The easiest would be in Magic ETL.
- Open the dataset in Magic ETL
- Add an Alter Columns tile
- Choose the column in the alter columns tile and select "Date" for CAST DATA TYPE
