-
I need to be able to change the Salesforce value field ex. USD $10000 to a decimal in ETL...
I need to be able to change the Salesforce value field ex. USD $10000 to a decimal in ETL, so that I can group by month etc.
-
How to change/reroute specific values in a field and leave all others the same
I have a table card. It shows the amount of deals closed by each sales team. In some cases, sales reps change industry teams. I want to be able to say "If sales manager = Joe Smith, then change team to Retail" but since there are 100 managers, I don't want to go thru and put each one in since the rest of the mappings…
-
Beastmode to Group All Months into a Total Month
Hello, I'm trying to use a multi-line chart where I already have my years broken out but want to have the months be seperate but also have a total. Can you please let me know what I am doing wrong in this beastmode. It "validates" but the "Total" never shows up: (CASE WHEN (month(`Start/Invoice Date`) = 1) THEN…
-
How to exclude current (incomplete) week from card
I am building a card that show number of visits at our practice. I would like to exclude the current week from the report as it is incomplete. I see other posts on how to to exclude the current month but nothing on week. Can someone show me the beastmode that will work for week? Thanks!
-
mysql transform unknown column
I'm trying to do what seems like a pretty basic transform on a salesforce dataset in Domo and it's throwing an error. Might very well be due to my lack of SQL expertise The database reported a syntax error. Unknown column 'ConvertedOpportunity.Name' in 'field list' The field is in the list, ConvertedOpportunity.Name is in…
-
Cumulated sum in a quarter using Magic ETL
Hello, can you help me to calculate a cumulated sum in a quarter using Magic ETL ? In this exemple: DateProductState Profit 15-jan-2018BicycleOregon $ 100,0016-feb-2018BicycleOhio $ 100,0030-mars-2018CarOregon $ 2 500,0010-april-2018BicycleOregon $ 100,0001-mai-2018CarOhio $ 2 500,0017-mai-2018CarMontana $ 2 500,00 the…
-
Multiple input datasets for a dataflow to start
I have a dataflow that has 3 input datasets. The dataflow has to run when all 3 have updated. The 3 input datasets update at various times and cannot be predecessors to one another. Is there a way to allow my main dataflow to run ONLY once ALL dataflows have completed? At present Domo runs the dataflows as soon as 1 of the…
-
Current Date BeastMode/ETL// Categorize Days to Months
I made a beastmode to substract the current date- a fixed date value which return in # of days. Now, I need to build a case to categorize the # days into months as I can't do it directly from the date configuration on the card. Do I require a magic ETL or a case can be build up on the same beast mode? Thanks, Jessica.
-
Round to 4 decimal places
Via beast mode or magic ETL I need to round to 4 decimal places so I can group prices charged to customers. Building reporting to identify which is the most frequently charged value. Need to group the pricing events so they match to 4 decimal places. I see the ROUND( function, but that appears to round to nearest whole…
-
Error joining data. The left input cannot include over 10,000 duplicates.
I have a magic ETL throwing the following error message: "Error joining data. The left input cannot include over 10,000 duplicates. Please switch your inputs, group your data, or remove duplicates before joining." There are 3 joins in the ETL. Each joins a reference table (5-100 rows) with a larger dataset (~50K rows). My…
-
Convert text field to date format
I need to convert a text field to a date field. I have tried magic ETL which didn't work. I also created a redshift using a case statement "cast(week_end as DATE) as week_end" , which resulted in a sytax error "invalid operation: error converting text to date". any ideas?? Thanks!
-
Net revenue retention
I am trying to calculate a net rev retention from X month to Y month (typically 1 months prior). This calc would compare revenue from all the accounts that paid us in X month to what those same accounts paid us in subsequent months. In the example below, the sum of the months would only campare accounts A,B, and C since D…
-
Determine the Week of the month in Magic ETL
I'm running a sales forecast dataset and want to be able to determine what week of the month we are currently in so I can calculate the weekly sales forecast target. Such as if the month target is 100,000 and its week 1 then the weekly target should be 25,000. I can add a date calculation column that gives me the Week Year…
-
Show sum of two averages in summary number (already exists in nested coumn)?
Thought this would be easy but haven't been able to get it so that BOTH of the average times for a `status` ('break' and 'unscheduled break'), which are part of a nested bar chart, can be used in the summary number - currently, we've only been able to get it to show 'break, not both totaled as is visible in the chart. None…
-
Window functions missing from MySQL
Hi, I'm trying to use SQL windowing functions with DOMO data flows, but they are not recognized. After checking for the version of MySQL, I saw that DOMO is still using an old version of MySQL: 5.6.28-76.1-56. Window functions make time based analytics much easier and would be great to have for serious analytics. Now I…
-
Inserting line break according to a character
Is there a way to break a piece of text into a new line in a table by when a character appears? Everything I'm looking at goes by length of the string of text, but in my case it goes by a semicolon separator. So I'll have in one cell a bunch of text of varying lengths and commas to separate that one instance BUT have a…
-
Syntax error near AS
In SQL Transform, I have... SELECT * WHERE (CASE WHEN (FcstName LIKE '%Fcst%' AND 'Rev Month' = 1) THEN ('Margin EV'*0.314/GM_Pct) ELSE 'Margin EV' END) AS MarginEVAdj FROM sales_pipeline I'm getting a syntax error "Near AS", but can't figure out what's wrong. Thanks!
-
Performance of UNION in RedShift
Has anyone used UNION successfully with RedShift in DOMO? I have a DF that seems to stall on the query using the UNION. I don't know if it would error out b/c I'm too impatient (after waiting 30 m), but I ended up revising how I wrote the script to not need the UNION...but was curious why it would have been delayed SOOOO…
-
How do you sort a calculated field as text?
Hi there! New to Domo and still learning.
-
Date Calcs in Magic ETL
Does anyone have any hints or suggestions as to running time calcs, like DateDiff (minute), for Magic ETL. I have some joins that I'm running in RedShift and I'd like to see about getting them into Magic ETL but with the lack of a function similar to DateDiff for time periods less than a day (I know there are date cals in…
-
Case aggregate within aggregate
Hi, I'm struggling with a beast mode. I'd like to know the count of customers that placed an order multiple times (repeat customers). The only way I can think of is the following formula: SUM( (CASE when COUNT(distinct `order_id`)>1 then 1 else 0 end) ) But I know that it is not possible because there already is an…
-
Rank of Customer
I'm trying to use the Rank feature. I want to rank the Top Record of Each Customer. I have a customer that can be listed multiple times in this dataset. I only want the record with the oldest date. I used the ranking, but it ranks the entire dataset, 1-8400, instead of giving me: Cust ID rank 123 1 123 2 234 1 234 2
-
Isolating the max date for a date and time stamp
Hi, I appending warehouse data on an hourly basis so we can see warehouse activity by hour by day by week and so on. I am trying to build a filter or indicator that will allow me to filter the most current timestamped date only when needed. I am using the below beastmode and it's not working. Any suggestions as I thinking…
-
identifying column of join data
let me know if domo checks all of [identifying column] when join data. I tried to join data with attached [TestData.xlsx], then got no result. I know when i put mached ID ahead of the Data1, I can get joined data, while mached ID are not always ahead of the data set actually. with above result i wonder if DOMO checks only…
-
Creating a new category field using existing fields within the data set
Hi, I would like to create a new field to categorize our different insurance payers into a few main buckets. The logic involves using three exisiting fields to determine which payer bucket each row (insurance claim) should be called. Is it best to do this as magic transform or do in a beast mode? Currently I am using a…
-
Building a Revenue Recognition Schedule
I need to build a calendar type schedule of revenue recognition, based on the dates scheduled. This would hopefully be available for previous and definitely for future time frame. The end result is to be able to determine, per group, how much revenue needs to be recognized in the current/previous month, and what the…
-
Beastmode Sales per day to reach goal
My president is asking for a sales dashboard. Total sales for Day, Month, and Quarter. Month and quarters have goals. Based on the sales totals today, how do I figure how many sales we would need per business day to reach our monthly goal, and quarterly goal. Basically he is used to seeing the attached, which also shows…
-
Count Distinct Number Changes
I've run into a really weird problem, that I'm curious if any one has ever seen it: I have a beast mode that counts the number of orders based on their unique identifier. The formula is: Count(Distinct `Name`). I have a combo bar/line graph that shows number of orders & revenue by month. I noticed that the order count was…
-
Max Value per Month on a Weekly Feed Dataset
We have a dataset that pulls information from SQL that gets updated by the managers once a week for their current value (document review counts and percentage rates for activity per day per user, effecitve rates, price per doc for review). We are using this for our weekly reporting just fine, but, when we try to set up a…
-
Fun with string functions
I do a lot of string functions to look at URLs. If I want to use a SUBSTRING and INSTR Function what's the best way to move the end a dynamic number of characters? EXAMPLE: I want to show the string after the 1st '=' and before 'the_end' in the 'big_string' field. I wrote:…