-
Break Line based on character for use in Mail Merge
Greetings All, I am seeking advice on how to add a break line to a string based on a specific character. I was able to get the table to look semi correct using ETL + Beast Mode. REPLACE(`String`,';','<br>') But I am unable to use my mail merge doc because the data doesn't doesn't export to excel correctly. I am a bit…
-
Day on Day Change Issue
Hi, I am trying to calculate the day on day changes of campaign activities (i.e. Sends, Receipts). The problem experienced is restricting the unique counts to one day because the code I've been using includes some records of the previous day. Depending on the time of viewing the domo cards, it takes the record of 24hrs…
-
Collapse columns based on value
I have a set of Call data where each row contains similar information in all columns but creates a new row based on the "flow" of the call. For example: LinkedId Leg LegType MenuName MenuOption Initial Queue Namebos-z15-call3-1550235827.1062618leg 1Dial curc-iready-qbos-z15-call3-1550235827.1062618leg 2Answer…
-
Running Customer Value
I am trying to build a card that show our customers' running total value (for the last 365 days). Here's the BM I have right now: SUM(CASE WHEN `SO Ship Date`<=`SO Ship Date` AND `SO Ship Date`>SUBDATE(`SO Ship Date`,INTERVAL 365 DAY) THEN `SO Item Total Amount` ELSE 0 END) Obviously though, when I graph it by month, I'm…
-
Dashboard not displaying Zero Sum results
I've built a dashboard for a variety of Adwords and Bing Ad results, and everything is working smoothly besides when I filter specific timeframes and the summation is zero, wherein it doesn't display anything in the Single Value field. I've created the following piece of code to try to mediate the issue and it doesn't seem…
-
How can I create a custom moving date range
I need to create a custom date range that moves with the current day. It needs to show the date range of the previous 10 days and the next 5 days based on current day. I can't seem to wrap my head around how to get started on it and if it can even be done in beast mode.
-
Adding year value to MM/DD date field.
I have a free text note field that we preface each note with a MM/DD format date. I am trying to add the year to this field so that Domo can chronologize this field and compare this free text date with things like system driven dates. I am currently using the following beast mode to extract the date from our text field,…
-
How to count unique value in a column
If I have a following list of data - 1,1,2,2,2,3,3,3,3,4,5,5,6,6,6 and I want to know how many unique value in this list, in this case, it's 6. How to do it in DOMO? Thank you!
-
Regular expression/RegEx cheat sheet and ETLs you can use RegEx in?
Hey all, I am extremely new to DOMO so I'm still adjusting to using this. I sometimes use the R plugin but I want to learn to use the ETLs as well- I am used to text mining and RegEx in R using stringR or grep. But, when I try to do the same search-and-replace operations using Value Mapper or Text Replace, my expected…
-
Mysql Flow - DATE_FORMAT not working.
I am attempting to format a java long date string in DOMO using: DATE_FORMAT(`createdDate`,'%m %d %Y') Here is a sample date string: 1549477680843 I see there are a few mysql functions not available in DOMO. Can anyone confirm this?
-
append rows
Hello all, I have a question on an append rows which isn't working, I have a field, equipment_item which is used for three different things, a Driver, a Tractor, and a Trailer, I can filter out which of those I'm needing based on the equipment_type_id being either, D, T, L respectively, but after I do my append and then…
-
Case to a Negative Number
I have a beast mode to calculate the distinct count of products from transactions. These transactions can represent shipments or returns. If the transaction is a return, I want to case the distinct count of the products to a negative number. I currently have the following but it is only returning positive values:…
-
Calculating weeks on hand based on forecast of subsequent weeks
Hi, I have the following dataset: Item Date Projected Inventory(PE) Forecast(FC) A Jan 28, 2019 604 50 A Feb 04, 2019 630 52 A Feb 11, 2019 1303 49 A Feb 18, 2019 1257 46 A Feb 25, 2019 1176 51 A Mar 04, 2019 1136 55 A Mar 11, 2019 1083 53 A Mar 18, 2019 1030 53 A Mar 25, 2019 980 50 A Apr 01, 2019 900 65 A Apr 08, 2019…
-
Syntax Error when using beast mode to create Case Statement for data flow
I received syntax error message `Invalid Formula : A column in this calculation did not exist` when trying to create a Case Statment (below) for a data flow. CASE WHEN `CaseCreatedBy` LIKE `%# Svc-ShipwiretoIM360integration%` THEN `true` Else `false` END
-
"Count not run DataFlow" - Why??
I am trying to run a DataFlow using data from an imported CSV. It says it's running then I get an email saying "Could not run DataFlow" but it gives me no information or error code as to why it will not load: "The DataFlow SPI_Salesforce_Pipeline_PROD_NEW was unable to run successfully." Any suggestions?
-
How can I calculate the percentage that a beast mode result is of a column set to 'count'?
Hi, this might be simple for some of you but i'm really struggling! Below is a table I have created. Columns 1 and 2 are using the 'count' functionality whilst column 3 is a beast mode that subtracts column 2 from column1. What I need to do is create a beast mode calculation that will indicate what percentage the…
-
How to replace an existing dataset
I'm trying to create a dataflow that increments a counter. Dataset ds1 has one record with one field containing a number. The dataflow should input ds1, increment the number by one, and output the number back to ds1, replacing the existing data. When I run the dataflow, it creates ds2 with the same name as ds1 but a…
-
case when something then blank
How would I write a case if I have a field 'link agency id' to the following, if the the link agency ID = BKR display blank or empty cell else display whateve the link agency id is CASE when `Link Agency ID` = 'BKR' then '' else `Link Agency ID` END
-
Calculating the sum of one column divided by the sum of another column - keep getting wrong answer
Hi, I am trying to get a calculation that measures how many hours it takes to generate a lead. In my data I have 2 columns - one called "group hours" and one called "group leads" In the best mode I have the formula set as: `Group Hours`/`Group Leads` I have added this value as a line (on a bar chart) onto a chart with this…
-
Error when trying to Declare a Cursor
I am getting a syntax error with the below code but can't figure out why. Anyone know why when trying to Declare a variable or cursor it doesn't work? Create TEMPORARY TABLE Foo ( MyDate datetime not null , Amount Decimal(12,2) ) Engine=Memory; DECLARE bDone INT DEFAULT false; DECLARE fme CURSOR FOR SELECT `Period Start`,…
-
Next Up Summary #
I want to write a summary number that notes the "Next Up" City in my data. X axis are city names (chronological sorted on Closing Date), Y Axis are values. Some of those cities are Closed, some are Ongoing - I can't use the series to break that status out visually by color, because I'm using a dual Y axis to show two sets…
-
Quickbooks Enterprise - ETL Transaction Pickup Issue
The nuts and bolt of the issue is our Quickbooks ETL logic is not picking up edits made by end users. We are wanting to pick up the latest transaction. We validate or process on the date and transaction number field. So say there is transaction number (TN) 123 on 1/29 and TN 124 on 1/30. If the user edits then edits 123 it…
-
Trying to filter out rows, where a certain column has NULL values
Hi, I have a dataset of almost 2 billion rows. I need to filter out rows, where a certain column has NULL values. What is the best and fastest way to do it? I tried creating a DataFlow using the mySQL option, but the flow has been running for more than 24 hours now. Could any of you please help in understanding how this…
-
Beast mode where the rank of the rows in a Dashboard can be introduced.
Is there a way to create a column in a Dashboard with sequential row numbers.? I am trying to repo the experience which exist in Excel. You update one cell with say '1' and then drag it down for all the rows. Filtering then is easy. For numbers of rows based on any sorting on a column in the result set. Thanks and Regards,…
-
Self Join Outer JOIN not working as expected
Hi, I am trying to do Outer Join on the same table. However I am not getting the result that is expected. I have the following dataset: Item Country Event Date Event Event Order A US Apr 29, 2019 Out Of Stock 1 A US May 13, 2019 Back In Stock 2 A US Oct 14, 2019 Out Of Stock 3 A UK Jan 28, 2019 Out of Stock 1 A UK Feb 04,…
-
How to transform from Excel Sumif formula to Domo ?
Hi, I have a data report with a template that looks like this in Excel: And I need to transform this into a card. So far I managed to make beast mode like this: Sum( CASE WHEN CONCAT( product_code, "_", account_id, account_name) = account THEN Value ELSE 0 END) AS 'Cost' But how do I take into account that the value for…
-
Summing column values based on dates
I'm trying to sum values as they fall within a range of dates. I think I have to do this in Beast Mode, because the Window function in ETL doesn't seem to allow for this though I have never successfully used it. I am not proficient in MySQL. I want to sum the 'EDP Visits' between Sundays. In the table below, the values for…
-
Case Statement with AND function in SQL
I am using below query to mark the two fileds as 'Exclude' SELECT *, CASE WHEN `account_number` = 20160001 and `document_number` = 57290 and `distribution_date` = '12/29/2018' then 'exclude' WHEN `account_number` = 40200001 and `document_number` = 57290 and `distribution_date` = '12/29/2018' then 'exclude' else 'Include'…
-
combining multiple rows into one
I have a table called continuity with for a specific order can have a driver, tractor, and trailer tied to that order, I'm trying to bring the data in by filtering my rows whether the type is tractor, trailer, ord driver then appending the rows together again, but nothing is coming up...could someone please provide some…
-
Date minus a few weeks
Hello guys, Am looking for code which states: Return X (actuals) between (current date minus 3 weeks) and (current date minus 2 weeks). The code I am currently writing is not returning the correct values for the correct dates. Could someone help me out please? Many thanks to the community,