-
Count Distinct with Or Case Statement in Beast Mode
Hi! Happy Monday! I'm trying to create a beast mode in which I'm counting the distinct sum of registration IDs regardless of the number of products that are attached to that registration ID except when the product name contains 'Guest.' So if a person registers for multiple sessions all of those are under the same…
-
dashboard not reflecting filtered values
I have a dashboard which has filters by semester term and college. There is a column for type of housing and a metric in the dashboard. The metric should display upon the filter on college as a whole as it is constant across all terms . However, it displays only upon filtering the terms, as the data is present across rows…
-
How do I search for rows containing specific text and replace the information in that row?
I have a column in my ETL dataset that is named "City", in that column there are multiple city names however they have been put in in all different ways (ie: st george, St. George, ST George). I would like to search that column for rows containing 'george' and replace the information in that row with 'St. George'. I've…
-
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…
-
Combining data in two separate columns
Can someone please help me learn how to combine data from two separate columns of a table? There are only two values in my pre-lease % summer 2024 column, and I need those two values to override/replace the values in the same row for the "pre-lease % 2024 column. I hope the attached screenshot helps.
-
Aggregating Data for a Beastmode - Counting Survival Analysis
Hi there -- I have a dataset that has an individual record per employee that states if they are active or terminated and that gives their current tenure or the tenure at the time that they left the company. I'm trying to do a survival beastmode using a bar chart where I show 100% at tenure = 0, and then at tenure = 1, I…
-
How to show a unique count for a different period than the selected date range?
Hi All, I have a card where I am showing a year to date breakdown of assets, in order to show this I have 'This Year' selected for my date range. However ideally I want to show a count of unique accounts as at the end of the prior year, a count of unique accounts as at the end of the current month and the difference…
-
Calculate a ratio
Hi, I am looking for a calculation that will allow me to look at a requisition number and then look at number of people Forwarded to Hiring manager and number who were hired and calculate a ratio For example the calculation should be able to say 3:1 for the below example 3 forward to HM : 1 hire Sometimes we have more…
-
Avg Time Formulas
I am having lots of trouble trying to figure this conversion out.. I have an excel file that I am bringing into domo.. within the card (table format) I am trying to get the average (as the average column already existing cannot be properly summed within domo). I am trying to take the 'talk time' divided by 'call id count'…
-
Creating Table Chart with Sales & Sales Last Year
I have a dataset where each dow represents a daily transaction 'date' for the 'productSKU', 'size', 'sales channe'l and also contains other columna where it sums the 'sales', 'stock on hand', 'units sold', etc I am trying to create a calculated field for 'sales last year' as a column for the 'sales' from a year ago for the…
-
Creating a new column using Formula in Magic ETL
Hello, I have a dataframe with a column named 'Screening Date' in that column are dates and null values. I want to create a new column called 'Completed' that looks through 'Screening date' and if there is a date in a particular row I want to Write "Yes" in my new column. If the 'Screening date' column is null I want to…
-
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…
-
Duplicate value handling in ETL
I have a column where there are around 34 values that are duplicated for one line that is supposed to be for two people. See picture for example. Ideally it'd be great to have one formula that can distinguish where the break would be so I don't have to manually create 34 lines of code, and have to continually add lines…
-
Comparing one month's EOM data to previous month EOM data in same row
Hi, I need to do a calculation so I can subtract previous months hours (4/31/2023) data from this month's hours (5/31/2023) data. This is how data is organized. This just a small example of how it is. Without changing the structure of dataset how can i get all the 4/31 data on the same row as the 5/31 data? I have been…
-
Confusion - LOWER
I have formula logic to try to standardize insurance company names. I use a series of case conditions to try to create a new field with standard naming. But my logic doesn't seem to work the way it would in SQL. My formula contains searches such as… CASE WHEN LOWER(insurance_company_name) LIKE '%aetna%' AND…
-
Organize values on X-Axis
Hi All, I am trying to sort my x-axis values on roles (see screenshot attached) not on the default sort options available in Analyzer. I read a previous note from 2017 that told me to use CASE to sort, so I did: CASE split_detail WHEN 'CEO'THEN 1 WHEN 'Head of Business' THEN 2 WHEN 'key management personnel(KMP)' THEN 3…
-
Beast Mode is valid but does not print all options, how can I fix it?
Created the beastmode below and although its valid I'm only seeing options "Low Performers" and "Other". The beastmode is suppose to look at revenue and at month-12 revenue and meet certain qualifications to identify it as either Non Public Videos, Low Performers or Videos Exceeding Lifespan. Thank you in advance! i.e. if…
-
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?