-
Time Zones
How do I change the time stamps in my postgresql data from UTC to EST? Saw the convert_tz() syntax from Domo but that's for mysql. Not sure where I should run this and how. Thanks!
-
How to create a Beast Mode calculation for fiscal year using a date field?
Hi All, I have a unique dilemma. I need to create a beast mode calculation to calculate our revenue for the latest fiscal year. I know I can just manually add the date filter criteria into the calculation or add a filter but the thing is the data is updated every day and we need the date filter criteria to be dynamic &…
-
Popular SQL Functions Missing in Domo
Domo, I saw a post from 5 years ago stating that SQL didn't have CAST or CONVERT. It's now 2020 and: * CAST doesn't even show up in the prompts * CONVERT does but doesn't work * ROUND is not even considered in the prompts. Please add these and other popular functions ASAP. It's painful these are not in there...Thank you.
-
Adding Varying Goals to tops of charts
Hi! We need assistance showcasing overall data in comparison to a goal. This goal will be different month to month and needs to have the ability to change. This is in beast mode and we would like to compare our actual opportunities to the goals we can set manually. Any help would be greatly appreciated.
-
Redshift order by
My first try at using Redshift. Trying to use ORDER BY and not getting the expected result. The output table should be sorted by column "a". Here are the steps: 1. Create tbl_data SELECT 3 as "a", 30 as "b" UNION ALL SELECT 3 as "a", 30 as "b" UNION ALL SELECT 2 as "a", 20 as "b" UNION ALL SELECT 2 as "a", 20 as "b" UNION…
-
Dividing with Multiple Beast Mode Functions
I have 2 formulas I am trying to divide into one another CASE WHEN `Action` = 'Lead' THEN SUM(`Unique Events`) END and CASE WHEN `Action` = 'Completion' THEN SUM(`Unique Events`) END Individually they work fine and result in 100000 and 50000 respectively. I need to divide Completions into Leads to determine my dropoff rate…
-
Magic ETL - string operations help
If i have an excel spreadsheet that contains the following column called file path: FilePathBUILDING\200303379 Files\Receipt20181213_20200107_133244.pdfBUILDING\200303379 Files\Receipt20181213_20200107_133309.pdfBUILDING\200410340 Files\HIDAWAY AVE 27227 (200410340)_CASE DOCUMENT.pdfBUILDING\29700587…
-
Calculating Date Time Stamps for duplicate ID's
I am tasked to create KPIs for how long it takes for an ID to sit in a specific status. Attached is a snippet of sample data for reference. The amount of days for the first "status change time' would be taken from the 'HRC Create date', or when the ID was created, the rest of the changes would be the difference between…
-
Quick Help
I keep getting a syntax error. What am I missing? CASE WHEN `invoice number` = 325940 THEN `customer code` = 'CS10003' ELSE `customer code` END
-
Anonymize a column of data
I would love to be able to convert or transform a column of data to anonymize it. For example, I have a clients data set that we use all the time but I would also like to be able to have the client column have a replica column where all the names are converted or anonymized. This way I can use the same data but keep the…
-
Find the percentage of count of value by category
Imagine there are 3 colums Country column - USA, UK, FR Purchase_status - Issued, Refund Count of purchased status The goal is to add 1 more column that shows the percentage of 'count of purchased status by country'. How should we do that in the beast mode, thank you in advance everyone.
-
Compare to text lists and highlight difference
I have 2 lists and am trying to highlight differences i.e. Value owned Managed values value 1 Value 1 Value 2 Value 3 Value 3 I want to highlight Value 2 So far I have used ETL to generate a new dataset with just these 2 columns. I am then trying to use beastmode using something like NULLIF or a case when 'managed values'…
-
Adding Columns in Calculator and losing values
I am adding two columns together in a Calculator function and where one column is blank, the sum is blank despite data in the first column. In other words, as shown below, red plus blue plus black should equal the figure highlighted. I have tried the data set as is (with blanks) as well as with zeroes. Any help would be…
-
How to use partition by clause in Domo SQL
I have following columns: Kingdom, Animal, Age, Weight Each row can be repeated multiple times. I want to sum values on the kingdom level in another view/table: My code is: SELECT 'Kingdom', SUM (MAX ('Age')) OVER (PARTITION BY 'Animal') AS 'Age', SUM(MAX ('Weight')) OVER (PARTITION BY 'Animal') AS 'Weight' FROM 'Table'…
-
Finding "earliest" date when one of them might be NULL
I have a dataset with a key field (loan number) and a series of dates. Within the ETL, I have to calculate several "Earliest Of" dates. So if the dataset has the following fields: Loan Number Date A Date B Date C Date D Date E I might need to create a new field called "Earliest of Date A and B". I am able to use the…
-
Case statement when two different fields are null
I need to filter a data set down to results where both the SQ and SQ_1 fields are null. How would I do this with a CASE statement would another approach work better?
-
Time to Second Sum Aggregation
I am having an issue with summing 4 different time to second functions. I have 4 different souces of time in the format of HH:MM : SS which were imported in as text and thus converted to seconds and then minutes. I'm unsure why they cannot sum as they should come out as whole numbers of minutes and when used seperately,…
-
Alerts for when Data Sets Fail
Currently if a data flow fails I receive an email alerting me to this occurance. However is there any way to have this type of email alert occur when a data set fails to ingest data from the connector being used? I've had some issues lately that I've luckily caught as we currently only have a few data sets from that…
-
Beast Mode Period-over-Period issues
Okay, so I'm trying to do 4 Beast Modes, and I cannot seem to get the right data. I'm trying to find the average amount of dollars per time period. Here's what I have come up with so far, to no avail: * Current year-to-date* avg(CASE WHEN `Date` >= DATE_FORMAT(CURDATE(), '%Y-01-01') THEN `Dollars column` END) or * AVG(CASE…
-
How to add color to hyperlink in beast mode?
CONCAT('<div style= ""><a href="https:`,' "target="_blank">',`id`, '</a></div>') I'm using this code in beasrt mode. How do I add color to my font?
-
Case When Aggregation compared to another Aggregation
I am looking for a way to get a count of accounts that have had a note closed within 4 days of it being opened. The issue I am having is when an account has a note that is closed within the 4 days but has had another note opened but is not closed within the 4 days within the same time period of the first note. example…
-
I need to export a list of attributes from my data table
Is there a way to create and export a list of attributes from my data table? I don't want the entire table with all of the data, just the attributes. Can I export this?
-
How to do generate_series in a SQL dataflow
Hi, I'm trying to elimate the need for a database between our production system and Domo by doing all ETL work within Domo (I know this may take longer to process). The database is PostgreSQL and some of the queries that I need to do in Domo use a function called generate_series, but it appears there is not a simple MySQL…
-
Recalculate the transformation of the dataflow based on the specific time range selected in the card
Hi everyone! I am trying to create a card to show the number of active customers. Active customers are those whose the sum of revenue and the sum of quantity are more than 0. Practically, it means to exclude those customers whose the sum of revenue and the sum of quantity are less than or equal than zero. The reason behind…
-
Beast Mode division calculation not work
Hello everyone, I've read other related posts and tried with my case but still failed, see if anyone could help. Thanks in advance! I try to get the % of certain rating (with 'green' or 'acceptable'), but I always get the result=2 with my formula. However, it returns the correct count when I do the numerator and…
-
trouble with str_to_date in beastmode
I'm having complications formating an existing date column to my liking with the beastmode function. The existing date column is currently formatted dd mmm yyyy hh:mm:ss AM or PM. This is in a table chart, the chart calendar is using the date column I just referenced, and is graphed by none. Right off the bat, it is weird…
-
Converting text to date in ETL
Hi all, I'm trying to convert a text into a date field in magic ETL, and I'm running into errors and unexpected outputs. Screenshots below. When trying to just convert the (numbers as text) into dates, I'm receiving the error "An invalid date was found". When I conver that column first from text to numbers there's no…
-
Beast Mode to change value in Column A based on Value in Column B
Hi there! I have a table that includes a Site ID column and a Site Name Column. I want to change the Site Name when the Site ID = x. I have the following code which works but it creates a new column but I don't want a new column, instead I want to change the Site Name in the existing site name column. Many thx in advance!…
-
Historical Database I inherited
Hi all. I'm dealing with CRM data, and have a snapshot of all of our data that is appended to a full historical daily database. This is done in a SQL transform, but I am completely unable to figure out why the input data is being appended to the output instead of clearing it. I am completely dumbfounded. I'm attaching…
-
Beast mode as filter
I created a beast mode to calculate $/visit, in my table chart, I wanted to show $/visit when it is great than 0. Any one has an way to work around since beast mode cannot be used as a filter? Thank you. Olivia