-
how to exclude all last Wednesday of every quarter?
How can I create a beastmode that exclude these dates from showing up in a card» 3/29/23 6/28/23 9/27/23 12/27/23 but also make it dynamic so that I dont need to hardcode these dates but it will exclude all last Wednesday of every quarter? thanks!
-
Get value for first day of the month
I need to be able to create a beast mode that does the following: for any given month sum up columns A, B, and C, then get the value D for the first day of the month, and implement the following formula: (SUM(A) + SUM(B) - SUM(C) / D for the first day of the month I can do it in an ETL, but that involves aggregations and…
-
What function will help me determine a variable part of text?
I'm using Magic ETL to create a new column that will return True or False depending on the contents of a string in another column. Question is how do I write a Case formula that will return True or False based on the contents of another column? For example, the word "Delivery" is placed in different locations on a string…
-
Issue with Sales Data Tracking and Date Transformation
Hello, I'm encountering an issue with tracking sales data in from our CRM. The problem arises from the fact that the table card relies on the 'Date Created' column for capturing engagements. Although this successfully captures most sales data, it fails to include engagements that were created in the previous year but…
-
SQL formula not working?
I have a series of tests that run to determine a pass or fail. All the formulas work but this one is giving me problems - CASE WHEN Critical/Major= 'Common' AND
No>= 2 AND Critical/Major= 'Major' AND No>= 1 THEN 'Fail' Any one know why this would not be returning a Fail?
-
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,…
-
Form Builder and Form Viewer Transformation
I am trying to use submissions we get from forms in DOMO via form viewer/builder apps and turn them into usable datasets that we can create cards with. Right now the form submission data puts the label in one column and the value in another and I would like to make it where the data looks like this: Date: | Model: |…
-
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