-
Beast Mode Appears to only be Calculating First Part
I have a beast mode that calculates the number of units based off the Period (month) and how far into that month we currently are. The beast mode was working fine but once we got into Period 2 the total is only calculating the total for the first period. Something is causing it to stop and I cannot pinpoint what the cause…
-
When x is null and y and z is not null then case statement - multiple parameters
Hey there I'm trying to perform a case statement in magic etl using the formula widget. I'm trying to use automl tool to predict the likelihood of receiving a like, comment, and/or shares based on a section topic. I'm not sure if I'm on the right path though. (CASE WHEN `likes` IS NOT NULL AND `comments` OR `shares` IS…
-
Beast Mode duplicated in New Card
Hello, I'm relatively new in DOMO and while creating new cards off other existing cards the Beast Mode was duplicating itself (ie started with one Beast Mode and after 8 cards there are now 8 Beast Modes). This is an issue because when I change the Beast Mode I'm having to do the process on every card. How can I get the…
-
Year filter on YOY comparison
Hi All, I have card that I need filtered by chosen years. I have a 5 year data (2017-2021) and need be able for users to compare 2 chosen years using a filter. I tried the min/max function but does not seem to return the right result. I used the below BM: To show recent year: case when `Year`= MAX(`Year`) then sum(`Gross…
-
Need to pull specific months from my Date column using Beastmode
Hey there, I'm trying to pull all dates from October and another from November. YEAR(`date`="2020")+MONTH(`date`="Oct") My date format is different though, it has the day first, then Month abbreviated, then year(full) 13 Nov 2020
-
Cumulative total of users with activity date
I need want to see how many active users we have over time. I have a field "last_activity_at", if it has a value (not null) they are active. And I want to add the sum total of active dates in the week they fall. I was able to accomplish this somewhat in beast most, but I think I need to use etl to filter out duplicate…
-
Long running ETL flows alerting strategies
Any strategies i can employ to alert myself whenever a query that generally takes seconds to run runs for longer than it should? I have a query that has on rare occasions been found running for hours when it should run in seconds. I'd like a way to be alerted to this situation without having to come across it by chance.…
-
Error in Case statement in Beastmode
Hi there, I'm getting this error when creating a case statement in Beast mode. Hoping someone can help? Invalid Formula : A column in this calculation did not exist. This is the case statement. It's pretty simple, and the column definitely exists in the dataset, so I'm not sure why it won't work. CASE when `Active days` >=…
-
How to read and understand error messages in Domo dataflows? (Redshift)
Hey, I am creating a dataflow and using redshift for my transformations. I am getting this error: The database reported a syntax error: [Amazon](500310) Invalid operation: syntax error at or near "then" Position: 888; and I have a couple parts in my query using a then and my query is going up to 33 rows. What is the best…
-
Card from Outer Join Two Datasets ETL
I'm trying to join two datasets, each dataset has a date field, how can I join the date field to get one Date Column that I can use on the X Axis, but will not remove any rows from the other columns. 1st dataset has: created date, complaints cases, end date, customer type 2nd dataset has: balance date, loan in books,…
-
Prior month metrics beast mode
Hi, I have a pivot table card which displays Last month projects, Projects 2 Months ago, Delta Projects. Below are the Beast mode calculations: Last Month Projects COUNT(DISTINCT CASE WHEN YEAR(`Date`) = YEAR(CURRENT_DATE) AND MONTH(`Date`) = MONTH(CURRENT_DATE)-1 THEN `ProjectNo` END) Projects 2 Months ago COUNT(DISTINCT…
-
Round to Millions in Column
Hi, I want to round currency to millions ("$1,500,000" becomes "$1.5M") for a column in a table that I am replicating from Tableau. The results in Tableau look to have just shifted the decimal point instead of rounding. Below I show regular rounding in blue and the Tableau results in green. Can someone advise how I can do…
-
Redshift does not sort properly in a dataflow
Hi I have the following query, which I sort by id and date. The id is sorted properly however the date is still random. The date is in a timestamp format, but I tried casting it to ::date, there was no effect on sorting: select distinct "id" app."App ID", app."Date"::date, app."Device" from "appointments" as app left join…
-
Time Difference larger than 24 hours
I have two date columns, and I want to find the time difference between them. Sometimes the differences between the times is larger than 24 hours. How can I get the entire time difference, making sure to also take the difference of date into account? In beast mode, TIMEDIFF(col1,col2) looks only at the literal time, and…
-
Simple DATEDIFF Question
As I understand it DATEDIFF will return (in number of days) the difference between two sets of dates. My dataset is pretty rudimentary so it seemed pretty cut and dry but when I do DATEDIFF (for days) it returns an incorrect figure. This is the beast mode I am using: DATEDIFF(`Form Complete Date`, `Form Sent Date`) I…
-
Dropping rows with null values (postgres)
I am building a redshift dataflow and found that the following statement allsow you to delete whole rows, but I get an error: DELETE from "table name" where "variable name" is NULL I use it in a table to transform my data and get tis error: Table transform must be a SELECT clause How come?
-
Copy Beastmodes to New Dataset
I have a case where I have a dataset that has to large of a date range that is causing a query to run for a long period of time. My solution is to create an archived dataset and a current dataset and then use a SQL join within DOMO to stack them. This solution has worked well to create a dataset I want, however, I have…
-
Creating a Table in a Redshift Flow
Can I create a table, insert rows and call it, all in 1 transform in a redshift dataflow?
-
Alternative to beast modes
Hi, We have a few beast modes with pretty lengthy list of our clients and we update it frequently, may I know if there is any alternative to a Beast Mode calculation to more easily manage those lists in various datasets. Thanks!
-
Agrupación de varios datos en categorias
Necesito ayuda para agrupar datos en categorías. ejemplo: 1. Lic tiene 50 datos disprsos y debo agruparlos y que me salga solo como Lic 2. Maestría se tienen 100 datos, se deben de agrupar a maestria Una vez que esta la agrupación en otra tarjeta visualizarlos desagrupados, pero ya con el filtro de la tarjeta agrupada…
-
Where Do SQL Transform Changes Take Place?
I am currently building a dataset that requires me to insert a dummy row if a specific date does not exist in a table. I am attempting to do this in a MySQL dataflow. From what I have read I cannot use the insert function when the transform type is a table. Instead, the transform type needs to be SQL. My question is…
-
ETL Pivot Help
Good morning, I have something I'm struggling with. In my screen shot attachment, the top table is how the data currently is. The bottom table is how I want it. This example is just 1 drive ID (157624 far left column) and it has 2 shift id's (257650 and 257651). For every different drive id, the shift id's will be…
-
Beast Mode Calculation for Specific Email Domain
I have a column called Owned by Email and I want to create a calculated field that displays "Yes" if the email address is @mydomain. Two part question, would that be accomplished better with a dataflow or would a Beast Mode shared on the dataset work as well? To clarify, there are half a million rows of data in the…
-
Unique Records in Beast Mode
I have three topics/cards with id's 11631,11634 &11636 and I have many users who completed these topics/cards. Now I want the list of all the users who completed all the three cards in a beast mode calculation. Base dataset would have columns like : card_id, user_id, Time
-
Copying dataset pdp settings
Is there a way to copy pdp settings between datasets?
-
Exclude header row and Totals row from dataset
Hi, Is there a way to exclude main header row and Totals row from a dataset? I have connected a SharePoint file to domo, the dataset has main and sub header row, totals row. I want the sub header to be shown as a main header in the dataset when I build the cards. There is Totals row at the end I want to exclude this row as…
-
YoY % Calculation
This is pretty straightforward and slighty embarrassed that I am stuck on this. All I need is a calculation for YoY % ((Current - Prior)/Prior). I can't seem to get it to work across multiple years. lag() does not appear to be available in Beast Mode. How can I calculate YoY %?
-
Precision Loss Redshift Data Flow
I am encountering a weird behaviour on using SQL SUM method on decimal values with DOMO Data Flow. Expected result based on query from MySql: Result in DOMO after Redshift Data Flow completes: Issue is that precision is lost in this case and final result will be different in doing SUM on this values: MySql => 100 DOMO =>…
-
Using ALTER TABLE in SQL Data Flow ?
I want to add a new column in my dataset while working in SQL Data Flow . Can I use ALTER TABLE command ?
-
Beast Mode to show total for previous week and two weeks prior
I am trying to create a table that has a column that shows a total from the previous week and a column with a total from 2 weeks prior. The data crosses the new year, 2020 to 2021. When I create the calculation for 2 weeks prior, it yields an accurate result but when I try a similar calculation for the previous week, it is…