Formulas

How to write a function that returns positive if function contains values from another column?
The title is a little confusing, but basically I want to write a function like this: CASE WHEN `Track Split` LIKE '%AZ%' THEN 'AZ' end EXCEPT, instead of basing it off of a string, I want it to pull values from another column. I have a lot of changing data from row to row, so I want the row to check to see if the data…
Possible to use a value that ignores filters?
I'm looking for a way to use a value in a beast mode formula that ignores my current filters. I've been wrapping my head around this problem and can't think of another way to solve it. Here is an example with my issue: Let's say my company has 5000 employees. There is a survey database with multiple rows of data for each…
How to get an avg in the grand total of a column with a BM with sum?
Hi, Im trying to use this BM CASE WHEN MONTH(Cycle)='10' THEN (sum($ New Annual Salary(in USD))/ sum($ Annual Salary(in USD)))-1 ELSE sum($ New Annual Salary(custom)(in USD))/ sum($ Annual Salary(in USD))-1 end and its sucessful but in the Grand Total I requiered an average not a sum, and usually I can use the agregation…
Using RegEx to split a column at a delimeter
I currently am working with a column that is delimited using underscores. I was able to write the following RegEx in a builder which was able to do what I needed: ^(?:[^_]+) What is the best way to implement this RegEx to return the part of the string that this captures?
Simple Mathematical Equations in Domo
Hello, I am trying to do simple addition, multiplication, and division equations using static variables or numbers in Domo. What is the best and easiest way to go about doing this? Using variables gives me "An issue has occurred during processing. We are unable to complete the request at this time." Using manually…
Question on breakeven formula
I am trying to think of a way to structure a formula to calculate days to breakeven when the initial item fixed cost has multiple sales tied it. I made a quick excel chart to demonstrate what l I am trying to accomplish where the initial item cost of $1000 took 6 days to breakeven.
Help with Beast Mode Formula
I need help doing a complex sum in a beast mode, as when I do it I either get an error or a totally wrong number. The formula I was given reduces to A/(A+B-C), and sums across the entire data set for whatever date range the user chooses. Each of the letter values will be either a zero or one for a given record based on…
Adding link to card name
I have this BM here that I have used in the past to link to data sources in tables so I can go directly to the respective dataset. Currently, I have developed a table card that includes card name and views in the past 365 days. I want the card name to be linked to the card itself so I can quickly click the link and delete…
Is it possible to calculate ROAS and WoW change in the same beast mode formula?
I'm working on a pivot table and I want to show the % change of ROAS from last week vs. two weeks ago. My dataset does not contain a 'ROAS' column so I'm trying to calculate it within the WoW beast mode formula. If I breakout the formula below into two parts (LW ROAS vs. 2WA ROAS), the first part will correctly give me…
Calculate the average normalized score in Beast Mode
Hello, I have data in this format: I want to perform the following calculation in Beast Mode and create a chart out of it: 1. For each Product, calculate the maximum possible score within that Product. 2. Divide the score of each employee (Name) in each Product by the maximum possible score for that Product. This is giving…
how to exclude all last Wednesday of every quarter?
How can I create a beastmode that exclude these dates from showing up in a card» 3/29/23 6/28/23 9/27/23 12/27/23 but also make it dynamic so that I dont need to hardcode these dates but it will exclude all last Wednesday of every quarter? thanks!
Get value for first day of the month
I need to be able to create a beast mode that does the following: for any given month sum up columns A, B, and C, then get the value D for the first day of the month, and implement the following formula: (SUM(A) + SUM(B) - SUM(C) / D for the first day of the month I can do it in an ETL, but that involves aggregations and…
What function will help me determine a variable part of text?
I'm using Magic ETL to create a new column that will return True or False depending on the contents of a string in another column. Question is how do I write a Case formula that will return True or False based on the contents of another column? For example, the word "Delivery" is placed in different locations on a string…
Issue with Sales Data Tracking and Date Transformation
Hello, I'm encountering an issue with tracking sales data in from our CRM. The problem arises from the fact that the table card relies on the 'Date Created' column for capturing engagements. Although this successfully captures most sales data, it fails to include engagements that were created in the previous year but…
SQL formula not working?
I have a series of tests that run to determine a pass or fail. All the formulas work but this one is giving me problems - CASE WHEN Critical/Major= 'Common' AND No>= 2 AND Critical/Major= 'Major' AND No>= 1 THEN 'Fail' Any one know why this would not be returning a Fail?
Value from yesterday
Hi guys, Im trying to pull in the value from yesterday to fit into a broader formula. The value is followers (social media). Pulling it in from yesterday gives me the latest total followers figure. Here is my latest attempt: SUM(`Followers'), DATE()-1) And broader formula is to achieve daily client account engagement rate…
Please help me write this filter formula:
How can I write a filter formula where I want to exclude everything that says 'INTERCOMPANY' from 'CUSTOMER TYPE' Column except this one scenario where we keep 'INTERCOMPANY' where there shows 'X' in 'COMPANY' Column. Similarly, how to write a filter formula where I can exclude everything that says 'ENDUSER' from 'CUSTOMER…
I wanted to rename few items in a column based on this campaign, in the ETL
Hi, I wanted to rename App to Web for this campaign, and I tried using a formula to achieve this in the ETL. Here is my formula CASE when campaign = 'CM1' then 'Web' else channel END But this is not giving me the expected output. Could anyone please help me with this? I actually wanted to achieve this in ETL only, not in…
ETL error: "failed to convert value 'Infinity' from type 'Floating Decimal' to type 'Fixed Decimal'"
Hi, I have a magic ETL dataflow. It has 2 input sources. There's a formula tile where we calculate a pacing percentage. (`Delivered Impressions` / (`Campaign Flight` - `Days Remaining`)) / (`Total Goal` / `Campaign Flight`) We've been using this dataflow and formula without any issue for months and now we're getting this…
First Non-Null Value from Column in Formula Tile
Greetings DOMO, Does anyone know how to find the first non-null value of a column in a formula tile. Tried using Coalesce along with a few tricks and couldn't get anything to work. All help would be appreciated. Cheers, Will
Creating a Pass/Fail Test in Magic ETL
I am trying to create a pass/fail test in magic ETL. There are four test it needs to go through to pass or fail. Once a row is listed as fail I would want all the rows matching that submission ID to fail as well how would I go about this or what is the correct formula to use? for example It failed the first test but passed…
Why is a field not allowing me to exclude it?
I recently built a new field for my dataset in Excel that added certain employees into specific groups. Most employees are not in these groups, so when I joined that field to the rest of my dataset most of the fields are blank. This should be fine. However, when I add this field as a filter into any dashboards or charts,…
Allow Window Functions in MagicETL Formula Tile
It would be helpful to be able to write MagicETL formulas using window functions, similar to in beast mode. While the Rank & Window tile is often sufficient, there are some use cases where having this feature in the Formula tile would simplify the dataflow steps: Using a sum window function with unbounded rows This does…
Form Builder and Form Viewer Transformation
I am trying to use submissions we get from forms in DOMO via form viewer/builder apps and turn them into usable datasets that we can create cards with. Right now the form submission data puts the label in one column and the value in another and I would like to make it where the data looks like this: Date: | Model: |…
Case Statement help
I have an ETL with several joins and calculations. My final output is good except for this last piece: I would like to identify in a formula that when the "ID" column has the same # but the "Status" column is different, then I want to keep the status result from the "last update date" column. Is there a formula I can use…
Help with Beast mode for getting the distinct count by order number for This year and last year
Hi All, Hope you are doing well!.. I am trying to get the count of distinct combination of ordno and partno by ordate ..Please find the input table below: The output table that I am trying to get to is Can you please help me with the beast mode for this..Also attached the excel file... Thanks, Arun
How to aggregate a case statement that uses a fixed function
Hello, I was hoping somebody could help me understand how to sum the following case statement. case when `Date` = max(max(`Date`)) fixed () then `On Hand by Day - DO NOT SUM` else 0 end I need the calculation to be dynamic and reference the date selected on the card. All attempts to aggregate the case statement fail and…
Converting string to date
Hi, I know there's a STR_TO_DATE function but maybe I could use more insight because it's not working. I have a date field that looks like this: 8-1-22 I want to convert this to a date. How can I achieve this with a ETL formula tile? Thank you.
ETL formula vs Beastmode - result discrepancy
Good afternoon, I've come across a headscratcher where a formula created within ETL is giving a different result when that exact same formula is created as a beastmode. In the attached image, the columns "island break" and "test1" are the exact same formula, with the former being created in ETL and the latter as a…
Calculate Previous Quarter Start and End Date
Hello! I am looking to calculate the Previous Quarter Start/End Date and the Previous Two Quarter Start/End Date. For example: Input: November 8, 2022 Desired Output: Previous Quarter Start Date - July 1, 2022 Previous Quarter End Date - September 30, 2022 Previous Two Quarter Start Date - April 1, 2022 Previous Two…

Upcoming Events

Connections Tour, Denver
Spend a half day with your peers, Domo enterprise customer speakers, and product leads to explore real-world use cases and new platform innovations. Whether you’re a power user or just starting to learn about Domo, don’t miss this opportunity to learn from like-minded professionals about how they use data to multiply their…
Connections Tour, Los Angeles
Spend a half day with your peers, Domo enterprise customer speakers, and product leads to explore real-world use cases and new platform innovations. Whether you’re a power user or just starting to learn about Domo, don’t miss this opportunity to learn from like-minded professionals about how they use data to multiply their…
Connections Tour, San Jose
Spend a half day with your peers, Domo enterprise customer speakers, and product leads to explore real-world use cases and new platform innovations. Whether you’re a power user or just starting to learn about Domo, don’t miss this opportunity to learn from like-minded professionals about how they use data to multiply their…
Connections Tour, Atlanta
Spend a half day with your peers, Domo enterprise customer speakers, and product leads to explore real-world use cases and new platform innovations. Whether you’re a power user or just starting to learn about Domo, don’t miss this opportunity to learn from like-minded professionals about how they use data to multiply their…
Connections Tour, Washington D.C.
Spend a half day with your peers, Domo enterprise customer speakers, and product leads to explore real-world use cases and new platform innovations. Whether you’re a power user or just starting to learn about Domo, don’t miss this opportunity to learn from like-minded professionals about how they use data to multiply their…

Formulas

Discussion List