-
How to filter for YTD Values
I have a dataset that goes back to 2018 and its format is: Month Day, Year 12:00:00. I was looking to filter for rows only with a data between 1/1/22 and 2/21/22 on ETL but when I try to do this through the filter function, it doesn't display anything even though this dataset is updated daily through a live dataflow. Is…
-
Rolling 30 Day Formula in magic ETL
Hi, I am trying to write the formula above within magic ETL. Column A consists of static values. Column B starts with 0 as the first value and has the formula shown in the image. The formula resets its count every time it gets to a number greater than or equal to 30. The only way I can find to do this is with a LAG()…
-
Client Churn ETL
I am trying to generate a data set for client churn by month and I'm running into an issue. I need to be able to calculate total clients (running total) up to a specific month and total lost clients that month. The final data should look something like this. You can imagine there were 5 new clients in November, 10 new…
-
how to aggregate an average case statement
Business task: "what percent of this region's sites beat their target signups YTD"? So I want to take the average daily signup for each site within a region and see if it passes its target. If it does pass, it gets a 1, else 0. After this, I then want to aggregate it to the region level. So for example if 5 out of 6 sites…
-
Domo IDEAs Conference - Beast Modes - Running Totals
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to calculate a grand total, running total overall and by month. Problem: How do I calculate a running total? Solution: We can utilize a window function which will calculate some metrics across the entire…
-
Average Lead Time
Hello, I am trying to get the average of lead time between submitted and approved this is my formula but is showing invalid formula when `FirstSetupApprovedDate` is not null then `FirstSetupApprovedDate`-`CaseSubmissionDate` / case when `FirstSetupApprovedDate`is not null COUNT(`CaseID`) end
-
SQL Row size too large
HI, I got an SQL dataset that was working well for over a month and yesterday I got the error message: the database reported a syntax error: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is…
-
Beast Mode vs Formula Tile on Magic ETL 2
Happy Friday Dojo Community! Does anyone know why a Formula would be validated by Beast Mode but show as an error for the Formula tile on Magic ETL 2? Also, is there any online guide that could potentially be helpful? Thank you! 😊
-
Pivot tile returning results slightly less than expected
I have a dataflow with 2 outputs. I branched off some appended data to one dataset, and then I took the same appended data and applied a pivot tile (initially I was joining to achieve the values in columns, but that was coming out way off). The dataset that is taking just the appended data is coming out correct, but the…
-
MySQL - Creating new column via LEFT JOIN
Hello! I am having trouble creating a new column (Dummy) to identify rows that I need to remove from my output in MYSQL dataflow (Beginner at using Domo). The query is valid, but when the output is produced, the column "Dummy" does not appear in the dataset. See below. SELECT A.`Posting Title`, A.`Weekly Reporting Date`,…
-
Create a summary number as calculation aggregate sum expense minus aggregate sum reimbursements
Hello, I am trying to create a beast mode calculation to show balance remaining after (sum of all expenditure) - (sum of all reimbursements). here is my current calculation. (case when `Expense Type` = 'Expenditure' then sum(`Account Balance`) else 0 end) - (case when `Expense Type` = 'Reimbursement' then sum(`Account…
-
Does anyone know how to convert Google Analytics "Date Hour and Minute"?
Does anyone have a solution they are using in a Magic ETL to easily convert Google Analytic's Date Hour and Minute output into something resembling an actual date time? ?
-
How to find number of data sources and magic ETL used in Dataset
Hi Team, We are trying to create a report with list of datasets with data sources used in that data sets and magic ETL used in that data set. Is there any way to pull this information ?
-
Creating an "unassigned" row for gantt chart visualization
I'm working on a gantt chart visualization for resource assignments, and trying to add an "unassigned" bar to the chart for any time periods when a resource is not assigned to a project. My dataset is generally structured as resource name, assignment name, start date, end date: I've pulled in the domo dimensions calendar…
-
How can we find data sources and magic ETL used in dataset ?
Hi Team, How can we find total data sources used in our dataset with all magic ETL used for that data sources ? Is there any way to pull datasources and magic ETL used in data set ?
-
Is there a strategy to manage data that would allow 2 owners?
Is there a way to have two owners for a dataset? We had a previous user leave the company. Now we have to re-assign a new owner for all those datasets but the reality is that those datasets might not be solely managed by a single user. Is there a way to share the ownership?
-
Framed Sum in Rank & Window Magic ETL not treating zeroes properly
Hi there, I'm trying to produce a rolling 12 month total for each month in a dataset, but I'm finding with some of the partitions that I get a negative exponential when I'm expecting it to return zero (all the months are zero - see raw data screenshot). It doesn't seem to happen with every partition that has zero values,…
-
Formatting Multiple Summary Numbers with Commas
Problem To build summary numbers with multiple metrics, one needs to use the CONCAT operator, which converts the entire output to a string. This means that numbers won’t contain comma’s. In this case, a solution is needed to dynamically add comma’s to these numbers based on their length. For example, in the Summary Number…
-
Adding Budget Rows to Actual
Hello, I have an original cases database in DOMO each row has a case submission and case start, and then I later replicate it all the columns and added the budget we have for the year. I had to unpivot this data in order to create graphs that will put both dates in the same axis. I would like to show the budget as a third…
-
Comma Separated Field to Child Table
I've written a connector to query a remote API, the data has come in correctly but each row can have many labels, because the connector will only create one table at a time and I don't want to query the remote API twice I've created a comma separated field. ID, Name, Labels 1, "Hi there", "hi, welcome, hello" 2, "Over…
-
Getting Previous Year Actual Sales to Populate
Hey all- I'm sure this will come off relatively simple but this one has me stumped. We switched ERP systems and I'm trying to get previous year sales to populate in a bar/line chart that shows current sales (by month in the bars) and previous year sales (by month in the line). I cannot get the new ERP system data to load…
-
Magic ETL Nested Case Statement
Hi all, I am trying to create a nested case statement (I 've provided an over simplified one that I've been experimenting with, but I have the same issue): (CASE WHEN `Specific Category`LIKE '%People%' THEN 'People' WHEN `Specific Category`LIKE '%Unknown%' THEN (CASE WHEN `Platform` LIKE '%Amazon%' THEN 'Amazon Likely…
-
Averaging multiple Percentile rankings
Hi Everybody. I am stuck with a problem. First of all, thanks to Jae Wilson for the percentile rank video. It help me solve one part of the problem. My data looks lik attached The data is pulled from Google Analytics and modified using Magic ETL. It contains page title, Month, and other metrics. The task on hand is to rank…
-
Combining datasets by looking for strings in one column
I have a couple datasets... 1) Product IDs with information 2) Google Analytics data with URLs that contain the product IDs on product pages I'd like to join the first dataset to the GA data to include product information that aligns with the product page. Is there a way to join these datasets in which I have the second…
-
Levels of Data in separate columns, bottom top approach
Hello, I am working on a request to provide all level of parent values for each individual child value. It is kind of bottom top approach. I have to start from a child ID column. For each child I need multiple cols of Parent ID. Something like below: The current data looks like I am trying to solve this in ETL. Any…
-
Number of Months Left As Displayed in Table
Is there a dynamic beastmode that counts the number of months displayed in a table?
-
Rolling Percentage
I want to have a rolling percentage of a value across a series of values. I want to be able to choose the value and the size of the series. Here is an example where I want to know the percentage of Oranges for the last 5 values. Also, Is it possible to do a running percentage of the value? Just thought of that. Ideally I…
-
Invalid date in MySQL datafow using Hubspot
Hey there, I'm attempting to join HubSpot data with another datasource using a MySQL dataflow but running into an error. In the HubSpot dataset, the closedate field is recognized as a timestamp, and looks great. In the data preview it filters correctly. When I add the HubSpot data to a MySQL dataflow, the column previews…
-
Trying to condense / group rows to remove nulls, any help appreciated
Hello, I am working on a data flow that needs to perform various substring functions to isolate data from a large xml string. I have gotten it close, where the data is linked to the log / time / user in in the sql database, and i am able to separate values into appropriately named columns. I would like to know if/how to…
-
Curved line + stacked vertical bar
Hi, I have two question for the card of Curved line + stacked bar(Vertical bar) I would like to change curved line "A" to "BGT". Please let me know how to change.(Question1) Now I have only one curved line in the card. But I would like to create two curved line of "BGT" and "B" in the card. ( I want to set "A" as vertical…