-
Right way to utilize string operation on long date.
I have a long date that I have converted to a text. Now I want to use the string operations tile to cut off the tail so I can have a nice clean date. the way it looks now in a string format is: 2022-02-24T12:08:34 What I want is: 2022-02-24 What I have been trying is: But it leaving the date unchanged and that timestamp at…
-
Best Practice: ETLs Vs SQL ETLs
We are trying to rebuild a tool Domo Tech built for us called Domo Stacker. Its main purpose is to aggregate many datasets together. Our current datasets are 250+ recursive ETLs that output a dataset that is growing (think: historical + appended new data) for four buckets of categorized data (four separate outputs when…
-
Adding Space to Postal Code TEXT String in Magic ETL
Hi, I might be missing something but looking for some help to add a space to a postal code text string using Magic ETL. I am trying to change a postal code from S4L1A5 to S4L 1A5. thx!
-
Adding business days to a date via MagicETL
Hi, I'm currently calculating a deadline using a dynamic formula in MagicETL. It looks like this: CASE WHEN `issue` = 'bug' THEN ( CASE WHEN `priority` = 1 THEN ADDDATE(`CreationDate`, INTERVAL 4 DAY) END ) The calculation works great, but I was wondering what would be the best way to update it to 4 business days, rather…
-
Creating buckets for unique ids to fall into, based on the sum of donations of those unique ids
Capturing donor frequency: We want to count the number of donations a donor has given per procedure. The amount of donations is the bucket I want donors to fall into. So, if we had 100 donors give 4 donations, we want those donors to fall into the bucket '4', and then we would count the unique donor ids. The part I am…
-
ETL Formula to Change a column of city, state, zipcode to Just portray State
I am trying to write a formula in ETL to change a column the has for example "Los Angeles, CA 90001" to preview as just "CA"
-
Forecast based on last month
Hi, I'm working on ETL created by someone else connecting 9 data sets. This is for a forecast, the March 29th, 30th and 31st are missing as the Feb only have 28 days. I'm able to join the Domo calendar and get the missing dates. For query we are using the date operator and adding the next month to it. I'm not sure how to…
-
Magic ETL Formula Editor Tile - Minor glitch
I am just wondering if anyone else has been experiencing this. I couldn't find any related posts. In the formula editor tile, I will literally click on the column name from the list below. When I am done with the formula and click "validate" I get an error regarding the column I selected and it says "table doesn't exist"…
-
Pulling out UTM parameters for new columns
I'm working with Google Analytics 4 data and am noticing some gaps with the campaign metrics. So, what I'd like to do is reference the page URL to look up UTM parameters and identify. In case you aren't familiar with UTM parameters, the way it works is you'll add a '?' and add these to your URL. Later in GA, you can tell…
-
Calculated Column Syntax Error
Trying to create column(s) in my data set to see if payments were paid early, on their due date, or late. I already have a column 'Due Date Minus Payment Date' 'DATEDIFF(`due_date`,`payment_date`) but for some reason this next column keeps giving me an error saying "oops something went wrong.) It seems incredibly basic, I…
-
Combining Row Data
Hi everyone, I've looked through all of the resources I can and can't find an answer to my issue so hopefully, someone here can help. I am working with student data from a university and am attempting to combine classes and labs into one row while maintaining data from columns. I have a unique identifier for each student…
-
Formula where value contains a single quote
I'm looking to write a formula where the value I'm searching for contains a single quote. Because Domo views single quotes as the start and end of my value, I receive a syntax error. Any known workarounds so that I can include a single quote without throwing off the formula? This is the formula I'm going for: (CASE when…
-
How to identify unique values in the column
Hello all, I want to label the unique entries in the column as unique and repeated entries as duplicate by using beast mode. Could anyone please help me on how to do this.. Attaching screenshot of the expected result table : Thank you
-
Count of date instances "last week"
Trying to visualize a sales funnel via Hubspot data and running into issues. I need to create a calculated column counting instances that a date field falls in "last week". Is there a quick calc for something like that?
-
Is there a way to show averages for last day of month
I have data (cumulative totals and averages) for a dataset by day for various products... I'd like to create a bar chart that shows the collective sum of the cumulative totals and collective average of the averages for the last day of each month... Can anyone advise on how to do this?
-
Can you find a Beast mode by it's function?
Hi We have a primary data feed and that datafeed contained a 15 digit ID. We recently updated the datafeed to the updated 18 character Account ID and in Domo we used to perform a String Operation that performs a left trim from 18 to 15 characters prior to using a Join Data tile. We wanted to find all of the Beast Modes…
-
Group by Percentage
Hi there! I'm trying to figure out how to get the percentage of rejected x-ray images by reject reason. I built a simple ETL that gives me the count of rejected images by reason for each type of x-ray (body part) along with the total # of x-rays for that body part (Acquisition). I'm stuck on how to get the % of rejects per…
-
Collapse all Subtotal Rows on mega table?
I don't see a way to collapse all subtotal rows, and it's frustrating. Without it, it's death by 5,000 clicks.
-
Creating complex ranking using certain values within a dimension
Hello all! I am attempting to create a sankey diagram that shows a customers activities leading up to a certain conversion. Each customer can have multiple conversions so I want to rank each activity leading up to a conversion, and have that rank reset after the conversion happens. Having some trouble theorizing how to do…
-
Error in Data diff function
I wanted to find avg daily registrations rate for which the formula will be: on 11th March 2022, we take registration count from Jan 1st to march 11 2022/70. 70 is the which is the total number of days till date. Formula I used: (CASE When `Year` = CONCAT(YEAR(CURRENT_DATE())) then COUNT(`Email`) end) /…
-
Error in DOMO documentation - POP: QTD Quarter-over-Quarter Comparison
Hi, I was trying to use Beast Mode that is provided here: https://domohelp.domo.com/hc/en-us/articles/360043430133-Sample-Beast-Mode-Calculations-Period-over-Period-Transforms#7.2., however the formula is not valid for Multiple Series - Previous Period. The error comes from additional ")" bracket that doesn't have…
-
Hi, I'm getting an error doing DateDiff bw two date fields where Nulls exist in one data
I believe my error popped up when I was trying to complete top two formulas. The Start Date field has some null values and where they're null, we're putting an arbitrary future date in so we can do a calc bw the Start Date and Payment Processing Month field. Thank you in advance.
-
Moving Values One Row Down - Conversion Rates
Hello! Does anyone know how to move the "Stage Count" column down one row (Stage Count 2) rather than erasing the top value? I am trying to get the Stage Count 2 column to read: blank 3821 336 122 33 32 I attempted a LAG, but this version of mysql is too old to use I believe :( Thanks, Keirsten
-
Use power function in beast mode or in the formula while creating ETL
I have 2 columns - revenue for yr 1, and revenue for Yr 5. I want to create a beast mode function to calculate the compounded Annual growth rate (CAGR) in beast mode. I am trying to do this approach but its not working Power (( 'rev Yr 5' / 'rev Yr 1'), 0.2) -1 but this formula is not working in Power function. I assume…
-
Calculated Field - Outstanding Invoice Count with date constraints
Hi there - likely easy one here - I am trying to create a calculated field to count outstanding unpaid invoices that are either before their due dates or within 14 days of their due dates. When an invoice is unpaid, the "type" field is empty (i.e. no payment type.) I've also created a custom calculated column with the…
-
Unique ID within parameter
I'm working on an ETL. I want to create a unique ID for each row of data with the caveat that the ID should be the same as the previous value if a value 0 appears in a flagged column. I was thinking of doing this through a window function of row number and then a function tile, but I can't figure out how to achieve this…
-
How to find the owners of the Beast Modes?
Hi, I am trying to build a card that shows the broken beast mode by owner name, but can't find the owner name. I tried to link the 'DOMO Governance - Beast Modes' data set with the 'activity user' dataset which gives me the owners of the card, page, and even dataset, but how do I find the owner of the dataset? Any help…
-
Recommendation on ranking multiple columns with different values
Wondering if anyone can advise or recommend how I would do the following. I'm not sure if ranking is correct for this or if it can be achieved using Beast Mode or if its completely impossible. I have multiple columns in my data that I want to create a rank for. Some of the columns contain Yes or no and others contain…
-
How to grant permission for a user to access an ETL dataflow?
I have a privledged user that has access to the dataset however he can't seem to access the actual ETL dataflow. How would I go around doing that? Thanks
-
Checklist to diagnose elongated runtimes from adding one simple join
I have a complex data flow (A) that was taking about 7 minutes to run. I have another simple data flow (B) that is taking 2 minutes to run. Both have outputs that are properly indexed. I created a new data flow that creates an inner join between two of the output datasets from each data flow (A & B). This inner join is on…