Best Of
Re: Trying to Split a Text Field with Time data (HH:MM:SS) into usable data
@psmith it turned out to be not too difficult to create samples for you in both versions of Magic ETL. Here is what they look like
Version 1
In the 1st string operations tile, extract the seconds by choosing Right 2. Extract the minutes by choosing right 5. (You're initially grabbing more than the minutes, but we will take care of that in the next tile.)
In the 2nd string operations tile, extract the minutes by choosing Left 2.
In the Replace Text tile, choose the minutes field you just created, enter : (colon) for the term to search for and leave the 3rd box blank in what to replace it with.
In the Set Column Type tile, choose your seconds and minutes fields you created and set them to type of integer.
Version 2
Use the Add Formula Tile and use the following formulas:
Seconds: RIGHT(`Time`,2)
Minutes: REPLACE(LEFT(RIGHT(`Time`,5),2),':','')
Use the Alter Columns tile to change the data type to integer for each of those. Results should look like this based on my sample data:
Re: Anyone know how the dropdown filter card was made in the Domo Covid-19 public dashboard?
@hamza_123 yeah you can use a custom app to do anything!
Ryan O and his team are the folks that build custom apps in Domo, but I can show you how I did a similar thing.
In a nutshell, the pieces you'll need:
Domo Dev Studio prereqs (ryuu etc.)
https://developer.domo.com/docs/dev-studio/dev-studio-overview
Then:
SQL API to query the list of values from the source dataset (the contents of the dropdown menu)
https://developer.domo.com/docs/dev-studio-references/data-api#SQL%20API
You'll use a component like
https://getbootstrap.com/docs/4.0/components/dropdowns/
to render the dropdown menu and feed the contents in.
then when users make a selection, OnUpdate, you'll call a function that passes a "filter" message from your iFrame (the custom app) to window.parent and filters the dataset.
In the domoJS there's a method built-in, but you can obviously customize it if you're doing a multi-select.
https://developer.domo.com/docs/dev-studio-references/domo-js
Re: How to get aggregated data without losing rows
@user094816 you can do this very easily in the ETL. See example in the image below.
Start with your dataset and then add a Group by tile and do a count of your column you want counted.
Add a join tile and connect it to your original dataset and your group by tile. Join on the appropriate key.
This will keep all your rows and show the total from the group by tile as well.
Re: conditionally start a dataflow
Hey @kjones140
I haven't tried this before but you might be able to utilize a recursive dataflow where you have an input dataset of the new data and also the output dataset as the input. You could then perform some auditing as a 3rd dataset on your new data and determine if it's good or not and add a column with value of 1 (success) or 0 (fail) (and a single row)
Your new dataset would have a constant added to it with an audit value of 1. Your existing output dataset would have a constant of 0. You then append the new data with the historical dataset and then join to the output of your audit path. This way if the audit passes (1) it would select the new data, if it fails (0) it'd select the old historical data.
Essentially you're conditionally selecting the dataset to use.
Re: Extracting data from Domo
in addition to @GrantSmith 's answers you can take a look at the ODBC connector, https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Other_Methods/Domo_ODBC_Data_Driver, there are commercial implications to aquiring access to the driver,but it does create a more SQL friendly workflow.
PyDomo would probably be the easiest option. In this tutorial we're executing a dataflow, but it's relatively easy to switch the use case / functions used to extract data.
regardless of which extraction method you used, I recommend you build a dataset view as the datasource and then subset that datasource to the correct columns and apply appropriate filters. you don't really want to be passing large datasets over API if you only need to pass a subset of the data (a few columns, or new rows since yesterday.)
Re: Extracting data from Domo
Hi @Brandon
You can schedule a report to send an email with a card at a specific time where you can choose to include the data as an attachment if you're wanting to export data that's been processed / visualized.
If you're looking to export raw data you can utilize the Java CLI tool (https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool) - specifically the export-data or export-to-s3 commands.
Alternatively if you're more technical / programmatical you can interface with the public API endpoints with the PyDomo (Python - https://github.com/domoinc/domo-python-sdk) or the RDomo (R - https://github.com/domoinc/rdomo) packages to extract the data. You could then subsequently upload the data to Oracle within the same scripts to simplify your data pipeline.
Re: Budget Amount and Gauge Card Setup
Angela,
Store your budget in a Webform or Google Sheets (two columns, amount and region name) then append (UNION) that data to your transactions. This way when the Budget amount needs to change you can just alter a webform. Very easy.
2) I want to see the total $ amount spent and % of usage MTD for each region inside the circle. I have not seen this done for a donut, is it just possible in a filled gauge? How can I get the % to show?
Consider a different visualization. If you had Spend and Budget or Spend and % of Budget Spent in a table card or a flex table, you'd communicate the same information in a more concise way. A gague is nice eye candy, but if you just had the donut it doesn't actually communicate the data with the same specificity of the % value you're trying to calcualate.
3) Does each region need to be its own card, or can all the gauges be on one card
See above recommendation to use a table card or a flex table or a bullet chart even.
Re: Show x-series minutes with no data.
What you'd need to do is have a dimension table of all times within a given day on a minute by minute basis. You can then utilize a DataSet View (instantly updates when underlying data updates. Might still be in beta. Talk with your CSM) to left join your data to this time dimension table. Then you can graph by the time for the last 60 minutes which would have nulls for the times where you don't have any data and force Domo to display those times.
Re: Average for Nested Bar instead of Total, or another option?
Have you tried utilizing the Line+nested bar chart type?
In this example I've created a simple beast mode for an average of my values for A1, A2, and A3 divided by 3 and plugged that into the Y Axis dimension.
(`A1`+`A2`+`A3`)/3
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Re: Struggling to write Beastmode that reflects current MTD values
@user048760 , @GrantSmith is spot on in the math. It will get the job done.
But this design / approach is ... difficult to scale because it requires you to never change the filters on your cards (notice how you currently have it set for 13 months. If a user wants to drill into a specific month or applied a date filter to the dashboard this card is hosted on, the card would 'break.'
This tutorial, is a bit of a stretch to wrap your mind around, but it's a much more scalable solution that uses an "offset date dimension" table design pattern that we consultants find ourselves using in Domo very frequently.
Either way. Good luck!