-
Group provinces by East vs West
Hi there, I'm stuck with a beast mode formula and hoping the community can help me out with this formula. CASE 'PROVINCE_CD' WHEN 'BC' THEN 'WEST' WHEN 'AB' THEN 'WEST' ELSE 'EAST' END Thanks in advance!
-
How to delete rows automatically based on a date in a dataset ?
I am inserting new rows in a dataset on daily basis, and now the dataset size has became huge since the rows are added daily. I want to delete the rows from this dataset based on date criteria (Dataset has a column type of "date" in it). So the criteria is like I want to preserve rows in dataset for last 30 days. eg…
-
Filling empty cells in a column with the last value
Hi there, I am currently stuck in a problem to fill empty cells of a column with the last value that is stated in that column. So I would like that the the cells of the column 'Cost Center Basis' are filled with the last stated value e.g. 'Kostenstelle/-träger: 1000010Bezeichnung: O001 General' until the next value is…
-
Redshift -- Significant Slow Downs Recently? (November 2020)
Good day Domo folks, Is anyone else noticing a significant slow-down with their Redshift transformations? My average run-time has doubled and in some cases, tripled or more. This started on-or-around 11/16/2020. I reached out to support but was advised "Domo has not control as it is AWS/Amazon". The MagicETL feature is…
-
50 day rolling average in ETL? Is it possible.
I have seen solution to a 5 day rolling average with 5 lines of code to build to 5 days, gets big with a 50 day average Do not want create 50 extra columns to build the rolling average.
-
Combining data sets without duplication
I have 2 datasets: * Contains individual transactions with an employee's name Employee Name Sale PriceSale DateJohn Doe100 11/2/20John Doe250 11/6/20Jane Doe50011/8/20Jane Doe10011/12/20 * Contains each employee and their number of consultations and new clientsEmployee NameStart DateEnd DateConsultationsNew ClientsJohn…
-
help with logic - finding unique number of `column1` that have BOTH value '1' and '2' from `column2`
I am struggling through working out the logic for this. Can anyone help me think through this? There might be an entirely different approach that is much easier. I am sorting out an email campaign and I want to know how many unique `EmailAddress` show up for BOTH `EmailName` value 'email1' and 'email2' at least 1 time. (To…
-
Magic ETL - filling values in a column based on other values in the same column
Hi there, Ran into a bit of a roadblock with my MagicETL. I was able to get to a certain point using the Rank & Window function but I can't figure out how to do the next step I need. Below is an example of where I'm at after combining two tables and partitioning them based on ID and sorting them by date.…
-
Reverse Running Total by Date
Can I get some help in understanding how to take the reverse of cumulative totals based on date? I've attached a sample of the data, which has US State and County cumulative data by date and I would like to calculate total from one date vs the previous date and keep the grouping of state and county. Any help would be much…
-
Bucket Series for 100% Stacked Bar Chart
Hi, I have a 100% stacked bar chart for sales that has about 350 options in the series `Company Name`. The units are the number of sales so: count(`Sale Amount`) I would like to bucket the companies that make up 1% or less of the total amount to simplify the legend. There is not a "limit rows" option for this type of…
-
Domo Percent of a Ratio?
Hello All, I have a simple horizontal bar displaying a ratio by reporting categories. For example, Category A - 27.9% Category B - 26.8% Category C - 25% etc. I would like to add a series that colors those bars by a sub category, but without losing the above. Right now I seem to get the % totals of the sub categories…
-
Best way to group data into bi-weekly pay periods
Several possible possible methods come to mind but each seem as though they could be more complicated than needed. I'm certain this must be a fairly common requirement so I suspect the best way to do this has already been established. My first thought was to use the master calendar and somehow append 'pay period start' and…
-
% Change calculation for Year Over Year QTD doesn't seems to be working
I am trying create a table with multiple segments as row and Current Year QTD Revenue, Prev Year QTD Revenue and % Change in Revenue. I was able to follow the documentation…
-
Similarity between two text columns
I have a dataset with two columns indicating company names. I was wondering what is the best way to determine the similarity between the two? Perhaps, I can pass 3 columns in R/Python and return 4 columns with cosine similarity. Can I do that? A starter code/example would be great?
-
Beast modes clean up
Hi Team, May I know the best way to clean up the beast mode calculations, the domo governance datasets[Beast Modes, Beast Modes with Fields, Card Fields and Beast Modes] have the details about Beast Mode ID, Beast Mode Name, Beast Mode Formula, Beast Mode Status, Beast Mode Saved to Dataset, Beast Mode Applied to Summary…
-
Shape Gauges inside Mega Table?
Hello, I'm creating a mega table showing previous month and current month and was wanting to create a column with up, down, no change icons and thought of the shape gauges. Is it possible to nest a shape gauge icon inside a mega table? Any advice on doing so would be appreciated. Thanks, Jason
-
replace special chars in string
I tried to specify /[^a-zA-Z0-9 ]/g and /[&\/\\#,+()$~%.'":*?<>{}]/g in replace tile both did not work. However /s to remove space worked....
-
Removing '<br>' text when exporting to CSV
Hi all, I am automating the export of a table to CSV that will feed my CRM software. One of the fields is a CONCAT of Text + other fields + line breaks ('<br>'). Unfortunately, when I export to CSV the line breaks are translated to text (i.e A <br> B <br> C), do you have any suggestions on how to avoid this issue. Perhaps…
-
Add current date to new row
My input file is from Google sheets containing sales. But they don't have a datestamp. I tried adding it with a constant in an ETL but the next day it overwrites all dates again with the current date. Is there a way to prevent this? There should be an easy solution but I can't see it. Anyone who can help me?
-
How to graph/program difference from average without hardcoding time frame?
We have a dataset that has 1 survey per row. Our ultimate end target is to show difference from average score by employee for varying time frames. For example if the survey score overall average was 85 and the average of John Smith's surveys was 88 we want to show +3. This can easily be programmed in and hard coded in ETLs…
-
Variance Calculation Question
Hey All, I'm trying to calculate variance on some usage data with multiple calculations. I need to be able to calculate the average of the 5 prior same days of the week. For example for the date of 8/16 to find the variance I would need to compare the average of 8/9,8/2,/7/26,7/19,/7/12. I also need to pull in average and…
-
Beastmode across seperate rows, is it possible?
Not sure if this is going to work at all or that I made a mistake. I've added 2 screenshots. One with the actual beastmode script and a screenshot from the table. Is it possible to exectue this script? I get a valid response but it doesn't return any values. Most likely because it's a script across multiple rows. Hopefully…
-
Adding a date field into an existing data set using SQL in Magic ETL
Hi Everyone, I have an existing data set that I'm trying to add today's business date to. I need to the data set with the business date field as a part of a data flow. I'm trying to use Magic ETL and use the SQL tool to "Add Transformation". For some reason, I'm not able to nail down what I think should be a trivial query.…
-
Legend Sort on Bar Chart
Hi, I have a bar chart where I want the legend to be sorted by the highest total units (from all the y-axis currently shown). Currently, it sorts off of the highest units in one particular client, but I want the legend to show the company with the highest total units on that view at the top. I have circled in red the ideal…
-
Concatenating records from a scheduled run at EOD
Hi everyone, I have a job that loads nearly 5k records (and ~100 columns) worth of data every day towards EOD. I want to be able to create a data set that concatenates the records the data from each day (say either for 2 weeks or 1 month). Ideally for the data set from each day, I will add another column (something to the…
-
Employee Turnover Report
I am trying to create an employee turnover report using a master list of employees, all time, with hire date, term date current status etc. Ideally would be able to use this as a running report to be able to see a snap shot of a time in the past - looking for active at start, # of new hires, # of terminations, active at…
-
Calculating the Running Maximum
Is there some way to calculate a running maximum either through ETL/SQL? I have employee hiring data and I want to check if an employee newly hired into a position is a backfill or a new growth. This classification is part of others that are used in existing charts showing how many active/hired/job change/terminations…
-
Sum of Distinct Count Variance
Hi I need help on below beast mode not working. I am trying to get the sum of all the variance using distinct count of customers. I am trying to get my total points of distribution gained. Thank you in advance. SUM(COUNT(DISTINCT CASE WHEN `Year`= 2020 AND `Month #` <=`Month # for Today` THEN `Customer Name` END )) -…
-
Beast mode: find most recent date for distinct ID and compare
I am trying to create the "Type" column for data in the format below. The logic: Find the row with the most recent "Date" for a distinct "ID" (second column) and compare it to the "Became Date". If "Became date" is NULL or newer than "Date": "Type" column is set to Prospect. If "Became Date" is older than "Date": "Type"…
-
Historical data for new year with beast mode
Hi We are coming to a new year, our fiscal year starts Nov, and wondering on how I can see historical data on my dashboard. Most of my beast modes are YTD like these 2 below. On the first month of 2021, my data will all change to just one month. How can I get a full year data for 2020 for historical review. Is possible to…