Best Of
Re: Converting to Integer
@Kyle_Russell It looks like the commas are causing the numbers to be imported as strings instead of numbers. Try this formula to remove the commas and convert the datatype:
CAST(REPLACE(`Amount`,',','') as DOUBLE)
Re: Converting to Integer
@Kyle_Russell Could you send some examples of values in those columns? If there are any extra characters, like dollar signs, in the original fields you will need to remove them before you can use the alter columns tile.
Re: Create Bar Chart with Current Month Vs Previous Month?
@deona720 You only need to include the word CASE once within a case statement, but every CASE must be concluded with the word END. I also recommend using the LAST_DAY function (which returns the last day of the month) over the MONTH function since it also accounts for the year. Your formula should look like this:
Case whenTitleLIKE '%OTT%' AND LAST_DAY(Date) = LAST_DAY(CURRENT_DATE()) THEN 'Current Month'
whenTitleLIKE '%OTT%' AND LAST_DAY(Date) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 'Previous Month'
ELSE '' END
Re: Combining data in two separate columns
@ScottLeeds I recommend using a beast mode with the IFNULL function to default to the Pre-Lease % Summer 2024 value when present, otherwise use Pre-Lease % 2024:
IFNULL(`Pre-Lease % Summer 2024`,`Pre-Lease % 2024`)
Re: How to Bring In Microsoft Intune and Microsoft Purview Data
Domo doesn't have a direct connector for those however you could attempt to utilize the JSON No Code type connector to see if you can get the data that way. Alternatively you can write your own custom connector:
Re: ETL calculation to work out hours taken from a start and finish timestamp
@robdmitchell There are a couple different ways you could do this in MagicETL:
- Date Operations Tile: Use the Difference between dates operation, with hours as the unit of measurement
- Formula Tile: Add a field with the following formula:
DATEDIFF(`DateTimeOff`,`DateTimeOn`)*24 + TIME_TO_SEC(TIMEDIFF(`DateTimeOff`,`DateTimeOn`))/(60*60)
Re: Retrieving Data from a partner's Domo sit
Agree with @MichelleH on the dataset copy. A couple other options if they are available are Virtual Datasets and Domo Publish.
Re: Retrieving Data from a partner's Domo sit
@Mendoooza Check out the DataSet Copy Connector, which can be used to transfer data between instances
Re: Aggregating Data for a Beastmode - Counting Survival Analysis
In this case, there are 2 possible paths you can take:
If drill down capabilities are needed, then you'll need to transform your data using ETL and create a beast mode that have copies of each persons entries for each tenure year in which they should be counted for. This required a bit more set up but reduces the number of BeastModes you'll need to achieved the desired result.
If drilling is not needed, then you can create several BeastModes, one per year and metric you want to achieve, as you'd need the same row of data in your dataset to be counted towards multiple aggregations. As an example, for Tenure X you'd have:
Active (Basically will only return the ID if that employee had been active for at least X years)
CASE WHEN `Last Hire Date` + INTERVAL X YEAR <= COALESCE(`Termination Date`, CURDATE()) THEN `ID` END
Terminated in period (those that made it to the tenure, but hae been terminated before reaching the next)
CASE WHEN `Last Hire Date` + INTERVAL X YEAR <= COALESCE(`Termination Date`, CURDATE()) AND `Last Hire Date` + INTERVAL (X + 1) YEAR >= `Termination Date` THEN `ID` END
Then your Survival % for X can be calculated as:
1-COUNT(CASE WHEN `Last Hire Date` + INTERVAL X YEAR <= COALESCE(`Termination Date`, CURDATE()) AND `Last Hire Date` + INTERVAL (X + 1) YEAR >= `Termination Date` THEN `ID` END) / COUNT(CASE WHEN `Last Hire Date` + INTERVAL X YEAR <= COALESCE(`Termination Date`, CURDATE()) THEN `ID` END)
If you only care about the % you can just create 5 of the last formulas and call it a day. Using the following as an example:
Then the calculation above will look as follows on a table (did up to term 3, to demonstrate)
Optimizing Week Calculation in Domo: Shifting Start Day to Monday
Domo's WEEK function typically starts the week from Sunday, which might not align with your reporting needs. We'll address this limitation and guide you on how to modify the calculation to start the week on Monday instead.
Solution: Adjusting the Week Calculation: Domo's environment might not directly support certain date manipulation functions. To overcome this, we'll leverage the CASE statement to conditionally adjust the date based on the day of the week. Here's a snippet you can use:
WEEK(CASE WHEN DAYOFWEEK('TimeStamp') = 1 THEN DATE_ADD('TimeStamp', INTERVAL -6 DAY) ELSE 'TimeStamp' END)
This query checks if the day of the week is Sunday and, if so, subtracts 6 days from the original timestamp to align the week with a Monday start. If it's not Sunday, it uses the original InitiationTimestamp. The WEEK function is then applied to the adjusted or original date.
Implementation Steps:
- Replace
Timestampwith your actual timestamp field. - Insert this modified SQL snippet into your Domo dataset where you're calculating the week.
By making this simple adjustment, you can seamlessly align your week calculations with a Monday start in Domo. This workaround ensures your reporting reflects the week structure that best fits your business requirements. Feel free to tailor the solution to your specific needs and make the most out of Domo's powerful analytics capabilities.
