-
MAX of SUM in Beast Mode Windowing Functions
Hello! Our org just had our windowing functions for Beast Mode turned on, and we're hoping to utilize it to calculate our MAX messages per second. Below is the logic I've gotten so far, but it does not work unless the table has all of the fields in it. We want to display the value dynamically, so we need to stay within…
-
Counts over time for things that have a start date and end date
Hi, I work for a recruitment company where we have contractors working. Does anyone know how I count the number of contractors out working over time when I have to take into account both a start and an end date? Without this I am unable do do any trends for my senior management! Many thanks Zena
-
Data Fusion not running
I've got two datasets 6.3M and 14.7M records. It's simple order and customer information tied together by a LONG (Numeric data type) ID field. Between the two data sets there's a total of only 64 columns so they're not huge but for whatever reason I can't get the data fusion to work. Occasionally I'll get the preview to…
-
No data to set up alert?
I would like to set an alert for when actual nb hours reach 85% of ave budget nb hours. When trying to set up alert I get this message: Set up your alert ruleNo alertable metrics found on this card. Please add a Summary Number or change chart types and try again. I'm not really sure how to correct my chart to get this…
-
Change in MySQL calculations
Has anyone else noticed that your MySQL dataflows started to evaluate expressions differently? Yesterday, I was using this where clause to filter for yesterday's date and it was working WHERE `Date` = CURDATE()-1 Admittedly, this is a lazy way of doing it... but, well... coders are lazy
-
Is there a way in ETL/Reporting to show the cumulative total for every month
I'm trying to show a dashboard where in I need to show the cumulative sum of the user count for every month. Need to have the month listed for which there is no activity also. In the above image, I have data for 2019-07-01 and then there is data for 2020-01-01. I also want to have data to show in between these months and…
-
Replacing Blank Values in Dataflow
I am creating an ETL dataflow in which I need to join my input dataset with other data sources via my column 'Sold-to Industry Code 1'. Although the values in 'Sold-to Industry Code 1' are numbers, Domo is treating it as text. This column is brought into Domo with blanks. I am trying to convert 'Sold-to Industry Code 1' to…
-
MySQL Dataflow Changing Column Formats
What I am trying to do is convert a bunch of time columns in my dataset that come over as text fields into time fields so I can aggregate or sum them. I am dealing with phone data, so I would like to add the call times together. I am fairly new with using MySQL dataflows, so any help would be appreciated. This is what I…
-
Beast Mode Calculation Timing Out in Card
Have you guys had any problems with a Card Timing out using a Beast Mode calculation. I wonder if my dataset is too large (338M rows). I'm doing a Count Distinct on a CustomerID. The card spins and then generates an error message: An issue has occurred during processing. We are unable to complete the request at this…
-
Not sure what etl function to use
I am trying to create individual columns for each contact type. Theya re connected to the prospect id (ex. each prospect has their own indivdual id). I cant seem to figure out how to ahieve this using magic etl. Any help would be greatly appreciated. Thanks
-
How to calculate other percentage in pie charts?
I have to create a pie chart in which one field calculation is like this Sum(X) / Sum(Y) * 100 which is giving the value of a particular category ( M ). Now I have to show other categories percentages like 100 - ( % of M ). How can I do this in beast mode or without beast mode using pie charts? How can I show 0.65 in other…
-
How to use datasets with duplicate column names in a dataflow
Using a JIRA connector, I import a dataset, which has a 1000 columns. There are two columns which have duplicate names. Consequently, I cannot use the dataset in a Domo SQL transform as it gives me a duplicate column error. I also cannot write each column name individually as there are 1000 columns (and I need 999). Is…
-
Grouping by the Hour and alerting based on count of usage per hour.
Hello, I am trying to do a group by using hour of day. The date operator in Magic does not have that funcationality but curious how I can do it within magic or in SQL. Here is my data where I want to count how many times a code was use in an hour for each day I want to use. COUPON-CODEupdated_atOrder #Goal(Count of Usage…
-
Single value Card - most recent data
I have a single value card that produces the number of hospitalizations today. My dataset is updated everyday at around 3 p.m. I have the date range set to today. I know there must be a way, beast mode or transform, to specify the value from yesterday if today's data is showing zero. FYI, the actual value will only be zero…
-
Calling a procedure in MySQL ETL flow
I'm trying to create a pivot table output using a stored procedure, but I'm getting an error while call the procedure. when I to execute the procudure, I'm getting the error as: Let me know what wrong I'm doing here. Thanks in Advance, Srinivas V
-
Previous Period Date Range - Auto Calculation
Hi all, I don't have a ton of eperience with Beast Mode/ETL, but was hoping to learn a little more from this. I'm looking to compare sessions for a period between two dates, to the previous period, also between two dates. So, for example, if I have an ad campaign that ran from 6/1/19 - 1/31/20, the previous period would be…
-
Convert a UTC timezone to Local Time
Hello, Experts, I am looking for guidance on how to properly convert time that comes in as UTC format from our MySQL server. I have converted it in the output within the MySQL code to be YYYY-DD-MM for the correct timezone but I want to keep the datetime feel to have HH:MM:SS. Is this possible? If so, how? Thanks in…
-
Clickable email link to Beast Mode field
Hi, I want to make a clickable email in a table. The email field is a formula, however, and I can't get the formula to validate. The email field I want to be clickable is "PrimaryContactEmail". Formula for PrimaryContactEmail: Case When Length(`ProcessorEmail`) = 0 Then `OfficerEmail` Else `ProcessorEmail` End I tried to…
-
Uncollapse Columns or Dynamic Pivot or ??
Greetings - I'm working on a Finance Dashboard and I've been given a data set in Excel that pulls metrics from various source files via a VLOOKUP; this is updated monthly with current month and year to date values. I can create most of the cards I need from this data, however, I need to calculate a new metric. The problem…
-
Decimals with more than 2 points
I have a file that calculates adjustments to salesperson goals. Occasionally the adjustments we make are less than a full day (-1) and end up involving more than 2 decimal points. In this case, I have 1.0669 which needs to stay as 1.0669, but is being converted to 1.07 in workbench. I have tried even uploading this column…
-
A way to count records open (but not yet closed) per dynamic period
Hello community, I have data that exists as such: RECORD ID, OPEN DATE, CLOSE DATE 12345, 01/01/2020, 02/15/2020 12346, 01/15/2020, null 12347, 02/01/2020, 3/31/2020 And I have the same data pivoted as well: RECORD HISTORY ID, RECORD ID, STATUS, STATUS CHANGE DATE 98765, 12345, Opened, 01/01/2020 98766, 12345,…
-
Option to persist a beast mode formula as a new column to a dataset for exporting purposes
It would be fantastic to have an option when creating a beast mode formula that allows the new column to be attached to the dataset for exporting purposes. Couple of use cases for this that would allow: * Ability to use the beast mode column in ETLs. To me, this seems like the highest value provided. * The column would be…
-
Alert Bases on Average for Day of Week
For a daily alert, we can not merely measure the difference between two consecutive days because day of week matters - we have much lower traffic on the weekends, a Monday peak followed by a typical slow decline. What I would like to do for an alert is something like this: For the particular day of week, if variance of the…
-
Fill in missing dates
I am needing to find a way to fill in some missing dates. My data looks like this. Project Start Date End Date Project 1 4/1/2020 5/1/2020 Project 2 4/10/2020 6/1/2020 I want to make a new rows for every date between the start and end date. I saw some way to do this with R but I am not familiar with R.
-
Filtering based on distinct count values
Hello, The discussions I've found that are similar to this seem to suggest using ETL, which I think I might end up having to do, but just wanted to see if there are any beastmode ideas out there. I'll refer to the column headers as Date, Collection Code, Donor, Donation ID, Data Entered?, Distinct Count of Data Entered,…
-
ETL/SQL or Beast Mode Data Merging Assistance
Hi, I need some assistance regarding the best way to perform the following. We have a report which is updated quarterly. On this report are numerous metrics of different types (strings, numbers, etc.). We need to be able to create a recursive dataset where we can (in HTML Table view) see a side-by-side comparison of the…
-
How to calculate week numbers based on custom week start day
Question Summary: I want to calculate the week number from a date based on a custom week start day. How can I do this, either in Beast Mode or SQL data transforms. Explanation: My company Domo settings have Weeks defined from Sunday to Saturday. However, one of my internal client team records weeks from Friday to Thursday.…
-
Index Data Calculation
Hi There, I'd like to index data but my beast mode seems to be off. Below is what I'm trying to do but the calculation isn't working. Index Formula = 100*(Current Revenue/Revenue from Jan 1, 2020) Beast Mode = 100 * (SUM(`revenue`) / SUM(CASE WHEN `day` = '01/01/2020' THEN sum(`revenue`) END)) Any help is appreciated!
-
OFFSET function in a table view
Hey Dojo! I'm looking to create a table that will dynamically show change between the row and the row above it. We need the table to automatically update the formula when one of the rows is filtered out (which makes doing this in ETL impossible). My idea was to do this with an LAG function or an OFFSET function, but since…
-
How to: Use a custom date range that rolls everyday
Hey everyone, I am currently stuck at a point where I need to find the difference between current date and the feb 22nd to plit my results. But the current date needs to be plotted each day from feb 22nd. I am using this for each day (Singel Value Chart) and it works, however I can't use this to plot a line graph as it…