-
Value from yesterday
Hi guys, Im trying to pull in the value from yesterday to fit into a broader formula. The value is followers (social media). Pulling it in from yesterday gives me the latest total followers figure. Here is my latest attempt: SUM(`Followers'), DATE()-1) And broader formula is to achieve daily client account engagement rate…
-
Please help me write this filter formula:
How can I write a filter formula where I want to exclude everything that says 'INTERCOMPANY' from 'CUSTOMER TYPE' Column except this one scenario where we keep 'INTERCOMPANY' where there shows 'X' in 'COMPANY' Column. Similarly, how to write a filter formula where I can exclude everything that says 'ENDUSER' from 'CUSTOMER…
-
I wanted to rename few items in a column based on this campaign, in the ETL
Hi, I wanted to rename App to Web for this campaign, and I tried using a formula to achieve this in the ETL. Here is my formula CASE when campaign = 'CM1' then 'Web' else channel END But this is not giving me the expected output. Could anyone please help me with this? I actually wanted to achieve this in ETL only, not in…
-
First Non-Null Value from Column in Formula Tile
Greetings DOMO, Does anyone know how to find the first non-null value of a column in a formula tile. Tried using Coalesce along with a few tricks and couldn't get anything to work. All help would be appreciated. Cheers, Will
-
Creating a Pass/Fail Test in Magic ETL
I am trying to create a pass/fail test in magic ETL. There are four test it needs to go through to pass or fail. Once a row is listed as fail I would want all the rows matching that submission ID to fail as well how would I go about this or what is the correct formula to use? for example It failed the first test but passed…
-
Why is a field not allowing me to exclude it?
I recently built a new field for my dataset in Excel that added certain employees into specific groups. Most employees are not in these groups, so when I joined that field to the rest of my dataset most of the fields are blank. This should be fine. However, when I add this field as a filter into any dashboards or charts,…
-
Case Statement help
I have an ETL with several joins and calculations. My final output is good except for this last piece: I would like to identify in a formula that when the "ID" column has the same # but the "Status" column is different, then I want to keep the status result from the "last update date" column. Is there a formula I can use…
-
Help with Beast mode for getting the distinct count by order number for This year and last year
Hi All, Hope you are doing well!.. I am trying to get the count of distinct combination of ordno and partno by ordate ..Please find the input table below: The output table that I am trying to get to is Can you please help me with the beast mode for this..Also attached the excel file... Thanks, Arun
-
Converting string to date
Hi, I know there's a STR_TO_DATE function but maybe I could use more insight because it's not working. I have a date field that looks like this: 8-1-22 I want to convert this to a date. How can I achieve this with a ETL formula tile? Thank you.
-
ETL formula vs Beastmode - result discrepancy
Good afternoon, I've come across a headscratcher where a formula created within ETL is giving a different result when that exact same formula is created as a beastmode. In the attached image, the columns "island break" and "test1" are the exact same formula, with the former being created in ETL and the latter as a…
-
Calculate Previous Quarter Start and End Date
Hello! I am looking to calculate the Previous Quarter Start/End Date and the Previous Two Quarter Start/End Date. For example: Input: November 8, 2022 Desired Output: Previous Quarter Start Date - July 1, 2022 Previous Quarter End Date - September 30, 2022 Previous Two Quarter Start Date - April 1, 2022 Previous Two…
-
Using RegEx to Extract Date
Hi all! I am looking for advice on extracting a Maturity Date from a column containing a string of text, example: string: 12months,MATURITY DATE:23-Jun-2022,BALANCE;$12345,PRODUCT Return: 23-Jun-2022 I am able to successfully select the date by using: \d{2}-[A-z]{3}-\d{4} But am unable to replace this so that it only…
-
How do I write the syntax for YOY % change?
Hi all, I am trying to create a calculate field where I am able to see only the change from year to year in average purchase price. I understand the math to be purchase price1 - purchase price 2 / purchase price 1 but I am not getting the correct output when I try the syntax Here is what i have so far: Any suggestions on…
-
Calculating hits per minute by timestamp and Parts ran
I am trying to calculate a live hits per minute by machine based on total parts ran per the difference between the start time of the day and the most recent timestamp. I am having trouble piecing together the correct formula to go about this. Does anyone have any suggestions? Thanks
-
How to Make a Weekly date range into a Daily Date?
Hello Domo Dojo Masters - Good Day! We have data from salesforce that only has a start of the week and end of the week date columns>>> How can I make it so that we can have a daily date?
-
Social Media: Follower Total for yesterday
Hi guys, I am trying to create a formula that Account Engagement Rate. As a part of the calculation I need to work out what the most recent follower count is for the account. This would naturally be for the previous day as this will be the latest update. Does anyone know how to achieve this? Is it something like: Date…
-
Beginner - managing a wide table, creating sum of..
Hi there, newbie here. I have a wide table with a column format a bit like this: name, question1, OPP1, EXP1, ACH1, question2, OPP2, EXP2, ACH2 I will need to make calculations for each row on the values (integers) grouped by OPP, EXP, ACH. I thought to create new columns with the sum of OPPx, EXPx and ACHx (where x…
-
Find greatest value for each specific ID
Hi, I have a dataset like this: ID Value 1 5 1 6 1 7 2 23 2 24 2 25 I want to write a ETL formula that creates a third column saying the greatest value for each ID is: ID Value Greatest 1 5 7 1 6 7 1 7 7 2 23 25 2 24 25 2 25 25 Thank you
-
How to represent how many tickets opened by each individual user?
Hi, I am trying to figure out how to pull 2 datasets together correctly. So, one dataset is Users and the other is Tickets. I want to connect Tickets to Users so I can have a single integer connected to a single user to show many tickets a specific individual has opened. Example: User_Name Number_Of_Tickets_Opened…
-
Any chance I can add in a percent change over various fields in a pivot table via window funciton?
I am doubtful that it is possible, but I'm holding out hope that somebody might have some insight. In excel I can take the values of two different cells per row to calculate a percent change, but in SQL I only know how to do this when it's isolated across a single dimension. Below is a screenshot of two pivot tables with…
-
Convert text to date
Hi All, I am trying to convert a string to date... STR_TO_DATE(`MFGDTE`,'%Y-%m-%d') But I am getting the value as 2019-12-18t00:00-06:00 ...Can you please let me know how to get just the date
-
Finding average of difference in two different dates
Hi All, I am trying to find the average difference in the dates between two different dates at a level of plant ,segment, model and part..I used the Group by tile for the same and in the aggregated column I am writing the below formula AVG(DATEDIFF(`cur_prm_date`,`prmdate7` )) over (order by `cur_prm_date`) for which I am…
-
Replicating SQL statement in Magic ETL Formula tool
Hi All, I am trying to replicate the following SQL statement in the MagicETL formula tool.. (SELECT MAX("FISCAL_YEAR") FROM table WHERE "DATE_VALUE" = CURRENT_DATE) AS CUR_FISCAL_YEAR In the Magic ETL tool I am giving the name of the output column as CUR_FISCAL_YEAR and Write Formula as MAX(`FISCAL_YEAR`) WHERE…
-
Magic ETL formula Help
Hi All, I am trying to use the following transformation for joining but I am getting an error...Can you please help! CAST(`DEALER_NO` as varchar(8)).. I am getting the following error... Syntax error in expression at character 20 on line 1 : cast (dealer_no) as varchar()) Thanks, Arun
-
Beast mode for count distinct by concatenating two columns
Hi All, I am trying to do the following beast mode operation: COUNT(DISTINCT (CASE when `ord_date` < CURDATE()-1 then CONCAT(`sales_no`,`part_no`) end )) The expectation is the distinct combination of sales_no and part_no ..But I am not getting that..Can you please help me here.. Thanks, Arun
-
Replacing single quote/apostrophe with nothing
I am trying to replace a single quote with nothing using the formula tile in Magic ETL, but it is giving me a syntax error. According to MySQL, you should be able to use a backslash as an escape character so that it will process correctly, but Domo is giving me an error. Any ideas?
-
Regex in Formulas in Views and in Formula ETL Tiles
I would like to be able to use regex to match values in formulas. Both in views and ETLS Such as this. I am using this to validate some data in a view formula CASE when `Field` like '[0-9]{4}\-[0-9]{4}\-[0-9]{3}' then 0 when `Field` = '' then 0 else 1 end
-
Global Formulas in ETLs
I would like to have a way to create complex formulas, save them (similar to the Beast Mode Manager) , and then have them available for use in ETLs; when an ETL that contains one of these formulas is executed the current version of the formula would always be used. This would have two use cases (for me): If there is a need…