-
Option to Remove the 10,000 Duplicate Error for ETL
Because of how fast/easy ETL flows are, I prefer to use them for most flows. My problem is that sometimes I hit the following error, "Error joining data. The left input cannot include over 10,000 duplicates. Please switch your inputs, group your data, or remove duplicates before joining". The Dojo suggests that this error…
-
Concat Summary # Issues
I'm having challenges getting my custom summary # to populate correctly. Below is my calculation, removed secure info with xxxxx. The formula is valid, but I'm just getting a result of 0 on my summary # without text or calculation. What am I doing wrong? CONCAT(avg (CASE WHEN `Offering Class Number` = 'xxxxxx' AND `Dim -…
-
Beast Mode windows function
Hey there I am trying to do a 5 day rolling change in value based on usage. I want to show previous five days compaired to the newest date. I have tried this beast mode: ((Lag (`QUANTITY`,5) OVER (Order BY `TimeStamp(use)`) +Lag(`QUANTITY`,6) OVER (Order BY `TimeStamp(use)`) +Lag(`QUANTITY`,7) OVER (Order BY…
-
Percent- Count Greater Than - Beastmode
Hey everyone, Please view screenshot below. I am trying to find the % of `Secondary Paid` that is above 0. I have been able to break it down with a few different beast modes but I am still not able to get the % of the total that is above 0. Paid = When `Secondary Paid` >=1 then 'Paid' Test = When(`Secondary Paid`)> 5 then…
-
BM Calculation Aged Incidents
Hi I am trying to calculate which of my incidents are Aged (over 30 days old from the current date), I have cobbled this together but it can't be working as I change the number and get the same result CASE WHEN DATEDIFF( CURDATE(),Max(`created_at`)) > 30 THEN 'Aged' ELSE 'Not Aged' end Could somebody help please? Kind…
-
How to transpose rows to columns using ETL
Hi, I have a large dataset that has thousands of rows, most of those rows have the same value for most columns except for one. I'm trying to transpose the value from the rows that are in one column to multiple separate columns, removing the duplicate lines. I know the "Pivot" function in ETL is what i need to use, but i'm…
-
Beast Mode: Sliding Window Function with Partition?
@jaeW_at_Onyx this is probably right up your alley with recent Dojo solutions and videos you have put together. Please see my video linked below, and attached sample data. https://youtu.be/Y21fV4t5kGk I have been able to do this with an ETL so for those that want to know how that is included in the video. I would prefer to…
-
Visitor Frequency Beast Mode & Card building
I have an activity log for every activity a user creates on our website. I want to calculate for a time period the number of users that have visited once, 2-5 times, 6-12 times and 13+ or some such off the activity date and user ID. In SQL I would create a dataset of visits by user ID then group by that - however I want to…
-
Beast mode to calculate month over month user count changes
One of the metrics we calculate as a business is our user change month over month. Our main data set we work off of is an activity log dataset which contains the date of the activity, the user id, and details about the activity. For active users we calculate the distinct number of users for that time period such as the…
-
Nested IF AND for Columns in a Pivot Table
Hi, I have an IF AND nested Excel formula I am trying to recreate in Domo. I want to create a new calculated field that classifies the order submission activity for the prior whole 7 months. This is what the result would look like for each of the options. Assume that May just started so we are just counting October through…
-
Creating Buckets in Beast Mode
Hello! So I'm trying to create temperature buckets in beast mode. Here's my formula: case when (`temperature` >= '33' and `temperature` <= '39') then '30s' when (`temperature` >= '40' and `temperature` <= '49') then '40s' when (`temperature` >= '50' and `temperature` <= '59') then '50s' when (`temperature` >= '60' and…
-
Create Data that does not exist
Hey all, I am working with some usage data for some of our products. What I am trying to do is be able to flag if there is zero usage on a account. our daily files only include usage but not if there is no usage. I have contract End dates that I can use to say something like if contract is active with no usage pull that…
-
Rename the week # for the X-axis
I have a bar graph showing data point in a weekly basis. I did graph the chart by week so the x-axis is showing week #1, Week #2 and so on. When the week # gets bigger it gets harder for people to know which week lands on which month. Is there anyway to relabel the week # to say week 27 of June 28th with a beast mode? or…
-
Fusion, how many rows is too many?
I have been watching @jaeW_at_Onyx on Youtube and am seriously excited by his data fusion methods. I would like to mimic his how to build a Trial Balance, Income Statement and Balance Sheet with no ETL but have 28 million rows in my GL. Would it be more efficient to split said 28 million into yearly chunks, first using a…
-
Beast Mode % of the total in Chart
I want to create a calculated value that show the % of the total. I have done this: SUM(Case WHEN `Country` LIKE '%In' then `Value` ELSE 0 END) / SUM(Case WHEN `Country` = 'TotalIns' then `Value` ELSE 0 END) But it displays anything although the formula is correct. I tried using only the first part of the formula and it…
-
nested if statement
I am trying to write a beast mode that would replace the following nested if statement out of excel: =IF(I8121="Total SS",M8121*12,IF(I8121="Total PODZ",M8121*12,IF(I8121="Total 5+1 ",M8121*2,IF(I8121="Total 3 Pack",M8121*4,IF(I8121="Club Pack",M8121*360))))) how would I format that beast mode? thank you for your help!!
-
Prepare Trial Balance using Oracle Managed Cloud data
We have migrated Oracle Managed Cloud data to DOMO and now trying to develop a Trial balance report. I have added an attached query to create Dataset. When I run the data set it is running continuously and no completion. I tried creating a small query to run but still the same issue. I am using MYSQL for the query. The…
-
ETL Addition empty fields
I am using addition to calculate 2 columns into a calculated column "Total 3". Example: Total 1 | Total 2 | Total 3 1 | 2 | 3 2 | 2 | 4 1 | | 4 | 1 | 5 Problem is I don't have data in every row. So as you can see the third one here results nothing instead of 1. 1 + "blank" is still 1. But it returns an empty field. Any…
-
Create a column by selecting a data point from multiple columns from ETL or beastmode
Hi all, I am trying to create a new column from multiple columns in my dataset to extract the data point that I need. Let's just say that there are three columns:A,B, and C that I am looking at to see which data point I need to use.I want all the data I use to be in the newly created column Z. If there is a name in column…
-
Syntac Error: Trying to group days into weeks using Case Function
Hi, I've been trying to group days to represent buckets of weeks. I code I've used is as follows: Case when `accepted_date` between '01/01/2018' and '09/06/2019' then '1 Wk Sp' when `accepted_date` between '09/07/2019' and '09/13/2019' then '2 Wk Sp' when `accepted_date` between '09/14/2019' and '09/20/2019' then '3 Wk Sp'…
-
Rolling Total in Table View
Hello, I'm sure this is easy but I can't seem to get this to work in the table view. Here is my data and my desired outcome is the "TY MTD Actuals". DayDay(Name)TotalTY MTD ActualsGRAND…
-
Update Quarters to Match Fiscal Year
Hi all, How can I update the format of my quarters to match our fiscal year? I'm trying to have Jul, Aug, and Sep represented as Q1 / Oct, Nov, and Dec represented as Q2, etc. Is there a beast mode formula for this? Or does this need to be updated under admin settings? Thanks!
-
Total % of an Item Against All Sales within a Beast Mode
Hello, I would appreciate any help on this one, I'm trying to achieve this in a beast mode to maintain speed. I have a data set, Order_date | Item _ID | Order_id | Source | Addon | Date | 1 | O_1 | site 1 | addon 1 Date | 1 | O_1 | site 1 | addon 2 Date | 2 | O_2 | site 1 | addon 1 Date | 3 | O_3 | site 1 | I can output…
-
How to Determine When Returned Orders Have Been Fulfilled
I'm struggling with how to handle this problem conceptually. My company delivers our products to customers and in the delivery tables, there's a field for Units Ordered, Units Delivered, Units Accepted, and Units Returned. This table is aggregated by month so the dates are always for the first of the month for the customer…
-
Table Subtotal not calculating beast mode
Hi, I have a table with below records. The subtotal for % column is simply adding the values instead of applying the beast mode calculation at the subtotal level. 2016 Total row % should be 34% instead of 49% and 2017 should be 35% instead of 62%. Has something changed with recent releases? YearSourceSubmission CountIssued…
-
Append in dataflow treats same field name from each input data set as a unique value
I have 2 data inputs that I'm appending to a new dataset. Dataset 1 and Dataset 2 both have a field called ImportDate of date data type. When i append the two tables using the append widget, a new column is added ot the resultling output data set resulting into 2 ImportDate fields on each row. I've triple checked that the…
-
ETL Copy or to SQL
Hello, I am trying to solve the following problem and seeing if I could do one or the other to solve this issue and best way to do it. Problem: I have an ETL that I want to copy. New inputs and different ETL to create new datasets. - Is there a way to copy ETL? From what I've read there currently is no way. If I am wrong…
-
Date Calculations - minutes and seconds
Hi, Experts, I am working on trying to find out how to calculate the time difference between dates. Here is what I am after: I would like to see the difference lets pick two dates start (created_date) - end (posted_date). I would like the output to show the time in minutes:seconds or minutes.seconds of the lapse between…
-
Return Capitalized (UPPER or CamelCase) Characters in Column Names from Redshift DataFlow
Any tips or tricks to avoid the auto-lowercasing of column names in Redshift DataFlows? The reason we need this is because of how page filters are case sensitive, and the page being filtered runs off of several datasets, some are the output from MySQL dataflows and one from Redshift (due to the size, ~4.5M rows). Would…
-
Connector & DataSet Issue
Hi I am using an out of the box Connector for my ticket data but the only two options are Append and Replace, I firstly pulled down 365 days and now each day I either have the option to do an append or replace, if I replace I lose 365 days data and left with 24 hours so I use Append (API limits mean I couldn't do a total…