-
help with margin formula
Im running the following formula to produce my margin %, and want when 'override_payee_id' = 'MEADBIMT' to have the % always be 15% but currently the way its written its taking .15/SUM(IFNULL(`total_charge`,0))*100 which isn't producing the correct amount, how can I write the following to have when the payee id is MEADBIMT…
-
Using FROM_UNIXTIME Correctly
Hello, I think this problem is an easy fix, but it has proven difficult for me. I've imported some data from Hubspot about my contacts, and I'm trying to convert one column from UNIX into a human readable time using a calculated field. (I validated it before and Domo said it was good to go). This is my formula:…
-
Beastmode Percentage Calculation
Hi There, I'd like to do a percentage calucation of the following beast mode. COUNT(DISTINCT (CASE WHEN `Campaign` = 'A' THEN `ID` END)) / SUM(COUNT(DISTINCT (CASE WHEN `Campaign` = 'A' THEN `ID` END))) Can you advise on how to do an embeded beast mode calcuation? Thanks!
-
How to Create a Dynamic Pivot in Dataflow ?
Hi, I have a dataset that I would like to pivot on a certain column value, development month, that introuduces new unique values each time the data is refreshed. The issue that I am having is that this field can change any time the dataset is updated. This is a rather large data set (2 million rows) and I'd like to avoid…
-
replicating Google Analytics’ Time Format in DOMO
I want to show Time on Site (and ither metrics that report in minutes and seconds) the way Google Analytics does, but it shows up in DOMO as number of seconds. Is there a solution at the connector level and is there a solution at the beastmode level (something I could do on the fly for reports do yesterday)? Thanks all,
-
Calculated Field to Calculate Fraction of Hours between Two Timestamps
I am trying to calculate the number of hours (w/fraction, e.g. 36.25) between two datetime stamps, one of these fields may or may not be populated. I have tried multiple "TIMEDIFF()" fuctions but I cannot simply get the number of hours between the two datetime stamps. I've come close but cannot seem to get negative hours…
-
Create a date field for TODAY at exactly noon
I wish to compare dates to see if they are before or after TODAY at Noon. the 12:00 noon part is static, but the TODAY part changes every day so I want that to be dynamic. I tried this but it did not seem to work: STR_TO_DATE(CONCAT(CURRENT_DATE(),'12:00:00'),'%m,%d,%Y') Appreciate the help!
-
Why does the percentage change calc in Beast mode show -100% all the time?
I have a beast mode calculation that calculates % change in sales but the result in domo is always -100%. Below is my bbeast mode code. Any ideas? (((CASE WHEN YEAR(Target.`ForDate`) = YEAR(CURRENT_DATE) THEN Target.`RetailSales` else 0 END) - (CASE WHEN YEAR(Target.`ForDate`) = YEAR(CURRENT_DATE)-1 and…
-
Comparing WTD Sales upto the current time for last 3 week
Hi, I want to compare WTD Sales up-to the same point in time for the past 3-4 weeks. The Period over Period chart only compares WTD sales at the end of the day instead of up-to the current point in time. How to achieve this in Beast Mode or Dataflow level? Thanks, Prajwal
-
Recreating Excel's Pivot Chart "Show values as % Running Total In"
I'm trying to recreate this Excel Pivot Table in Domo (Currently as a Heat Map but if someone has a better suggestion I'm all ears). (See "Domo - Pivot 1.JPG) The good or bad thing is I can select the data from the database however I want so I'm free to do calculations before the data gets to Domo but the basics that make…
-
Dynamically Uncollapse Columns for Tabular Output
Hi, I'm trying to create a stored procedure like in the link below and while its not erroring out, its also not uncollapsing columns like it says it would. I'm not great at SQL and I've tried this in ETL but I can't get it to work there either. https://knowledge.domo.com/@api/deki/files/5209/Dynamic_Pivot.sql?revision=1…
-
Getting DateDiff to show decimal places
We're using two date fields in a card and are trying to show a difference between the two. The source data shows these w/decimal places (screenshot) but our card didn't, couldn't understand why but when viewing the data table at the bottom of the card, it seemed it might be caused by the PickupDate being YYYY-MMM only,…
-
User defined functions for Beast Mode?
Is it possible to create user defined functions that can be called in Beast Mode? I'm creating Beast Modes for summaries in the top left of a visual, and it's getting ridiculous & unwieldy pretty quickly. In order for it to look reasonable, I need to format and abbreviate the numbers, similar to how it's described in this…
-
Remove Duplicates - Keep Most Current Instance?
I have a table of Opportunities from Salesforce that I am pulling into Domo, and Appending on a regular basis, for any Opportunities that were modified by a User or Automated Process in the last day. The purpose would be to have the most current records of all fields, for every Opportunity. What I'm finding is that I most…
-
Adding an email address to table
I'm trying to add a clickable email link to a table. From reading through other posts, I know it has to be some form the CONCAT below. When I try to use what I've written, it doesn't do anything, doesn't open an email message, nothing. What am I missing? CONCAT('<a href="mailto:',`Email`,'">','Send Mail','</a>') In the…
-
Round Down In Beast Mode
How do I round down to the nearest thousands. For example, 17,692 should be 17,000...not rounded up.
-
Can you use REGEX in a Beast Mode?
Easy question for a Monday morning. Can you use REGEX in a Beast Mode? I have found references to REGEX in ETL but nothing about it in a Beast Mode. Thanks and enjoy the softball.
-
Joining two datasets in ETL using SQL
Hi, I am using ETL magic transform sql function. Input: Dataset Transform: SQL query let's call it table A Output: Select * from table A It is a really simple sql query which should take a maximum of 10 seconds to compute. However, when I save and run the dataflow it is currently on the 10 minute mark. Any idea why this is…
-
Case statement with multiple field conditions
Hi - I'm looking for a Case statement that will provide multiple conditions and multiple "then" statements but keep getting an error. I've tried multiple versions but none have worked. Examples below: CASE(`Impact Rank`) WHEN <(`BDS Rank TW`)THEN CONCAT('<span style="background-color: #00A86B">','OUTPERFORMING') WHEN…
-
How to create a beast mode calculation that can do a count and then sum
I’m currently working on an issue dealing with conducting a filter than an aggregate in beast mode. (I will say at this point that I have a feeling that this may not be done in Beast Mode.) I have my data that looks something like this: Col A Col B Col C (What I need to create) B 2 0.2 G 4 0.4 H 4 0.4 The total of Col B is…
-
Join twice on a different column (only 2 input datasets)
I've looked but unable to find anything to indicate it's been asked or that it's ever been addressed but we're looking to join an input dataset 2x (same column) to the other input dataset but on a different column each time; is this possible? Example: Table 1: Conversation Assignee and Conversation Rating Assignee (Dataset…
-
Last 4 weeks accumulated beast mode
Hello, I'm trying to write a beast modes that shows the last 4 weeks as Last Week, the previous 4 weeks before that as Previous Week, and the last 4 weeks prior year as Prior Year. The reason why I will create 3 beast modes is because only want 3 columns showing for these accumulated week periods, versus showing 52 columns…
-
is it possible to create generic transforms available to all datasets/dataflows?
I know this has been asked before, in different ways and different contexts, but I am hoping to create a general (ie object-oriented) framework that standardizes common ETL tasks across multiple datasets. I know I can create a Magic ETL dataflow which applies a common transform across multiple inputs and gives multiple…
-
Using DataFlow, what is the difference between adding Transforms and adding Output DataSets?
I am new to MySQL DataFLow in Domo. Can someone explain the difference between Transforms and Output DataSets, and when to use each one? Thanks!
-
Adding missing day rows
Hi, I am trying to get data for everyday in a particular date range even if there is no data for that day. For example, Item Location Date Count A USA Jan 3, 2018 5 A USA Feb 10, 2018 12 A USA Feb 25, 2018 13 A Canada Jan 7, 2018 14 A Canada Feb 10, 2018 10 A Canada Mar 3, 2018 5 Desired O/P: Item Location Date Count A USA…
-
Beastmode Between Specific Dates
How can I write a beastmode to return data between two dates? For example CASE WHEN 'Date' >= 10/01/2016 and <= 09/30/2017 then 'value' else 0 end Thanks! Sarah
-
beastmode week start
I have a client whose week starts on Monday. since the default week start day in Domo is Sunday, I would like to use beastmode to calculate week numbers with a start day of monday. I get a validation error every time I try to input a number in the [mode] portion of WEEK(date,[mode]). I'm referencing MySQL 5.5 documentation…
-
Converting currency
I have some data which contains revenue but in USD, because the data vendor is US-based, but all our other numbers are GBP. Any ideas on a way to convert tjhis into GBP? Perhaps pull some public currency data into a table and then I can do a currency calculation in beast mode or a tranform? Mainly interested in where and…
-
CSS in my beast mode calculation appears as a text instead of corresponding to actual value.... CONC
I've this in my bease mode calc and when I use this under "Guages"... it displays "the css code itself like '<div style="font-size: 20px;">'"... CONCAT ( '<div style="font-size: 20px;">' ,CONCAT ('Total Daily Unique Visitors:', round(((sum((CASE WHEN `scenario` = 'AC_TY' THEN `daily_unique_visitors_scenario` ELSE 0 END )))…
-
Ignore filter on a calculated field
Hello, I'm wondering if it's possible to create a BeastMode that ignores date filtering... I have a dataset that contains the following information ID# CUSTOMERNAME STATE FullDate Logins Errors % The card I'm building should only display the data from yesterday BUT, I want to add a field that calculates how many times the…