-
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…
-
Using aggregation in case statements
I have a data set of sales where a sale is either a "new sale" or an "add sale". This value is stored in `calcSaleType`. I would like to graph the ratio of new sales to add sales over time but keep turning up null values. The value of a sale is stored in `total_cost`. I've tried the following with no luck: (case when…
-
beast mode for latest 12 months
Hi I need help creating a beast mode to show monthly gross rev for the latest 12 months. I have example on how I get my YTD gross rev below. I not sure if it can be dome in beast mode or any other way. Your help will be appreciated. Thank you in advance. SUM(CASE WHEN `Year`= `Year for Today` AND `Month #` <=`Month # for…
-
Create a calculated column using beast mode
Aloha: I would like to calculate the following using beast mode, having the results in a new column. Example: column name: Start Date "9/1/2019" column name: Days Ago "2" If column "days ago" has a number in it, then take the date in the "start date" column and subtract it from the number in column "days ago"; resulting in…
-
Beast Mode: Dependency Identification
Hi. I am trying to do a dependency status table for the project lifecycle. I currently have columns for Project name, associated Project Status, and Dependent Project (which other project is required for this one to complete). I am trying to write a statement to insert another column that identifies the status of the…
-
An issue has occurred during processing. We are unable to complete the request at this time.
Hi I am new to Domo and have no SQL knowledge. I am trying to get our YTD cases quantity with some condition taken from other working beast mode. If someone can please help me figure the beast mode below why it's erroring. Thank you ion advance. SUM(CASE WHEN `Year`=2020 AND `Month #` <=`Month # for Today` THEN (SUM(CASE…
-
DOMO Cheat Sheet
I made a cheat sheet for end users to create a card with beast mode functions. I hope it will be helpful for all.
-
Help with LEFT function in Beast Mode CASE Statement
I am having trouble with the LEFT function in a CASE statement. It works as expected outside the CASE statement. When I use this beast mode: LEFT(`Material`, 4) I get the first 4 characters in the Material column. When I use this Beast mode (CASE when `Process`= 'Lathe' then 'Lathe' /*LEFT(`Material`, 4)*/ end) I get…
-
Beast Mode to compare Values in 1 Column against 2 time periods
Afternoon, I have access to a dataset at my organization that pulls in competitor data. The question I want to answer with my card is: * Who is/are the new competitor(s) that showed up last week (fully completed week) compared to competitors from last 12 weeks (exluding the prior completed week) I have built out a dash…
-
Beast mode count distinct based on subset of data
Hi - I am trying to count distinct patient ids based on a subset of data. My beast calculation is shown below. To explain further, I am in need of the unique patients who are first in the initial group (have at least 1 record with one of the indicated procedure codes), and then, of those patients, how many also have a…
-
Tableau formula Conversion to domo question
Hey all, I am trying to create a formulas in my ETL but keeps giving a syntex error. I'm trying to translate this tableau formula: if ([Prior 5 Days Avg]) > 0 and sum([Current Day]) > 0 Then (sum([Current Day]) - ([Prior 5 Days Avg]))/([Prior 5 Days Avg]) elseif sum([Current Day]) = 0 then -.99 else .99 end Into a DOMO…
-
Datasets Which holds Certified Cards/Datasets and CCPA Certified Cards related Information
Hi Everyone We need your help on below task. We want a dataset which is capturing the certified cards/datasets and along with this we want a dataset which is capturing CCPA Certified tags information. If you want anything more from my side please let us know. Thanks Akish
-
Adding row numbers to a table with a Beast Mode?
Is there a way to add row numbers to a table with a Beast Mode? So there would be a column to the left of Date with a 1. for 2020-09-01, a 2. for 2020-09-08, etc?
-
Beast Mode: Reverse Date Count
Hi All, I have a database containing project information updated on a day by day basis. A project will have an associated RAG (red, amber, green) status and the corresponding date. For example, Project X was red for 10 days, then green for 3 days, and red for 7 days up to today. I need a statement which, first, identifies…