-
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…
-
Comparing days of data 364 days apart
I can't believe this isn't as simple as I think it should be. The dataset has transactions which have a corresponding date value. I need to create a column to compare values for dates 364 days apart. Seems like it should be so simple. This beast mode gets me the current day units: IFNULL(`GG_ELIG_UNITS`,0) (the column set…
-
PDP - Social Users
Hi, We have a PDP enabled dataset and we want to send a scheduled report to our social users, the social users do receive an email but they are not able to view the card with data. Enclosed is the snapshot of the card received via scheduled report. Please advise. Thanks
-
Rolling Count Current Week
I am trying to create a table with rolling count of the current week, last 4 weeks, and last 8 weeks. I was able to create beast mode calculations for last 4 & 8 weeks, but I am lost in creating beast mode calculation for the current week. What would be the best way to calculate rolling count for the current week? Thank…
-
Beast Mode Case Statement Pivot Table issue
I have a Beast Mode calculation that has a case statement that looks to pull actual data from the past and projected data from the future. Everything works fine in the pivot table card with the exception of the total. It appears that the case statement is affecting the total instead of the pivot table just summing the…
-
Previous Month
So I need to create a BM to give me the previous month, I use this as a filter for a report to send people the previous month's data. I found that with the year change my current formula does not catch December if it is January. I am looking for a filter to give me the rows where Entry Date is during the previous month.…
-
What's wrong with my Beast Mode Calculation?
When I tried to show the results from the queries below I get this error, "An issue has occurred during processing. We are unable to complete the request at this time" AVG(sum(distinct`Users`)/ `Content ID`) AVG(SUM(DISTINCT `Users`) over (order by `Content ID`)) I want the average of users over content id, but the results…
-
BeastMode Lead Function Doesn't Exist - Rolling Average
Hey guys, so I'm trying to apply this concept to my dataset, but when I tried to apply it I received the error, " Invalid Formula : This calculation is using a nonexistent function." Apparently, the lead function doesn't exist in the BeastMode library but it did when this video was created. (6) Domo - Analyzer - 3 month…
-
Listagg function or equivalent
Trying to do the following calculation so i can have all the available strings in a given column aggregated with a comma delimiter. Basically trying to correctly use the listagg function which appears to be quietly supported by domo, but still having trouble. Has anyone been able to use this function or get around it some…
-
Get unique records from datasets
Hi, I have two reports for last and current month which has size, folder name, date as fields. There are duplicates between last month's report and current month's report. When I combine both the reports, the output dataset should not double up - meaning if there are same folder name from previous month report, it should…