-
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…
-
Filter to Last 12 Months (dynamic)
I've got two data sets I am trying to combine in Magic ETL to calculate inventory turns. In the sales data I have 2 1/2 years of history that updates weekly. In the ETL in preparing the sales data I need to filter to just the last 12 months (from last date in the set). Since this last date updates each week I'd like to…
-
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,
-
can i use the between operator in a case statement in a beastmode
I'm trying to write a CASE() statement in BeastMode that is similare to using the BETWEEN operator in SQL. I've tried this but BM will not validate. ( CASE when `FieldValue` <= -16 then 'Bad' when `FieldValue` > 0 then 'OK' when `FieldValue` between -1 and -15 then 'Good' else 'Ugly' end ) I've also tried this way. BM will…
-
How do I delete records in an ETL data flow?
Good Day, I have an ETL data flow that is recursive in nature updating Google Analytics data on a daily basis. A subset of data was missing so I appended another dataset in the flow which was ultimately in error. I am attempting to delete the data which was ultimately in error and replace it with new data. I'm not sure how…
-
Help for nested case statement in beast mode
I have these two measures made in beast mode that looks like this: Status CASE WHEN `Delivery` = 'Done' THEN 'Complete' ELSE CASE WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) THEN 'Pending' ELSE 'Complete END Completion Percentage ( COUNT(DISTINCT CASE WHEN `Delivery` = 'Done' THEN CONCAT(`Company Code`,…
-
how to use the percentage of a category as the summary number
Hello, I have this card that looks like this (Showing only one month's data) What I want to show in the summary number is the percentage of expired category(14,131 / 8,961+14,131) that is made available when you hover over the chart (61.19%). How do I do that for the summary number? Thanks!
-
Beast Mode assistance (issues w/month-over-month calculations for 12 month table)
This is a followup to a thread from last month (Table, compare several month-over-month). We've created a table card that for the most part function as requested, but noticed a few things that need to be accounted for and we haven't been able to figure those out at this time. In the screenshot below, we have a…
-
Creating an arbitrary date field unrelated to data to hold every date that exists
I'm trying to recreate a chart that compares Created to Resolved issues (based off JIRA data). How it looks in JIRA below Where i'm running into trouble in Domo is that I think JIRA just creates a random new date field/range on the fly that isn't included with the data when pulled over. In Domo I have to choose what field…
-
How do I translate these report format in Excel to Domo?
Hi, I have these tables that looks like below: The formula shown is for J12, and going from J12:V16 they have the same syntax of formula like that. Other numbers are manually input or linked from other sheets. Now, how do I replicate the report format and the formula in Domo? I have tried using Beast mode to solve this,…
-
Splitting comma delimited keywords and combining in one column
Use case: For every account I have a set of comma delimited keywords stored in 1 column (think "SaaS,business,b2b,etc"). I ultimately want to create a list of all unique keywords, then compare that against my dataset to measure frequency ("How many accounts contain "SaaS"?) My feeble attempt: I've managed to split the…
-
Calculating hour differential over multiple days
I'm trying to calculate the number of hours elapsed between when a given event occurred and the current time. Both are formatted as "DD MMM YYYY HH:MM: SS". Basically, if the event occurred at 01 Jan 2019 12:30:00, and the current date is 03 Jan 2019 12:00:00, I would like the output to be 47:30:00 Thanks!
-
SUM of CASE in Beast Mode
Hello, I would like to create a beast mode which can give me a % of a column in a table (e.g. % OCJ Actual) like such: Marketing IntentFY GoalOCJ Actual% OCJ ActualCOLLABORATION5,000,000500,00010%SECURITY5,000,000500,00010% The beast mode formula seems correct but I am getting this error 'An issue has occurred during…