-
3+ Consecutive days
I'm creating a card where it shows the 3+ Consecutive days if the Delegate is No I only want to display 3+ consecutive N but right now its' displaying all the dates. (CASE WHEN `Delegate Y/N` ='N' AND DATE_SUB(`Date`, Interval 3 day) THEN 1 ELSE 0 END)
-
Group Sales Data by Hour by Day
Hi! I have a dataset that has a timestamp as the date column. I need to sum the sales by hour for each day. I need this done in Magic ETL so I can do another part of my problem before putting it into a card. Is there a way to do this? I have been playing with 'Group By' tile and 'Rank & Window' tile but can't seem to…
-
How to append data to the output dataset?
I have a workbench job that will run daily. It will get the data with current date. I then needs to filter this data and create two data sets using the DataFlow ETL tool. How do I append data to the DataFlow ETL output table? DataFlow ETL creates a new table every time it runs. I realize that One way to achieve this is to…
-
Anything like Difference matrix, Difference between two pivots, difference within the value field?
I've searched around but haven't had any luck finding what I need, but I am not sure if I've searched the best way either so please refer me if this is a duplicate. I'll use dummy data to ask my question, but effectively I need the difference between two values across a couple of dimensions. The goal is different from our…
-
Beast mode difference shows blank even when using coalesce
@MarkSnodgrass and @GrantSmith, you both helped me with my last question and for the most part, it gets me in the right direction. The problem seems to be that a null value completely nulls the whole result (like in sql), even when using ifnull() or coalesce(), whereas excel does evaluate it correctly, albeit with its own…
-
Count the number of times one day appears in a month
How can I count the number of Mondays (also need it for Tuesdays, Wednesdays, etc.) in the previous month?
-
Nested joins in Data Fusion
Can someone please help me to understand how to build nested joins using Data fusion? Should the links be added in a single data fusion or should i use nested data fusion?
-
Comparing values based on their update in google sheets
Hello, I am stuck with a very simple yet hard to design dataflow in domo. So just to elaborate my problem, I have a some google sheets where data gets updated once or twice in a month by our different sales team members. Now they have asked me to build a dashboard where I could show them difference between the values or…
-
Domo IDEAs Conference - Beast Modes - Rolling Averages
Greetings! This is another post highlighting a beast mode from my Domo IDEAs conference session. This one covers how to calculate a rolling average. Problem: How do I calculate a rolling average? Solution: We can utilize the LAG function to get the prior X values and then divide the sum by the number of values. Lag:…
-
Updating a DataSet Triggered by the Finish of a Glue Job
We are using a Connector to retrieve data from Amazon Redshift and display it in Domo. We use AWS Glue to retrieve data from Oracle and store it in Redshift, but I don't think it's possible to trigger the end of the Glue job to update the DataSet in Domo. Currently we schedule the DataSet update around the time the Glue…
-
Handling multiple aggregation levels from source data
My company uses syndicated POS data from sources like IRI, NPD, and retailer POS. There are many measures that cannot be aggregated from the item level data, so I need to bring in pre-aggregated data in addition to the non-aggregated data. I was planning to have one column that indicated the aggregation level, but when I…
-
Beast Mode for Aging Bucket
Hi, I'm writing a beast mode to create the aging bucket, it's is showing results but it's has everything under < 15 days. Note sure what I'm doing wrong here. The 0.06% in 15-30 Days are the negative # (CASE WHEN ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN…
-
What's wrong with this case when statement / how do case when's operate on the back end?
Hello! I'm having a problem with a case when statement, and it seems like its not working because I don't fully understand how/when Case When's output information, especially when nested. Here's the code for my new variable `CurrentWeekofYear` case when `Week Number` = (case when `WorkDate`=TODAY() then `Week Number` end)…
-
Line breaks and special characters are being added to a webform
We use a webform for our Sales roster. We have had an intermittent issue with line breaks and special characters being added to the rep's names which causes quite a few issues. The very first sales rep on the roster is getting a line break added to her name. There's another sales rep getting multiple line breaks and…
-
Failed parse text 'NULL' from column 'Column Name' as type 'Date'
Does anyone know how I can solve for the error msg below: Failed parse text 'NULL' from column 'Column Name' as type 'Date' I get this error msg when the ETL finds a NULL value in a column with data type 'Date'. Thanks in advance.
-
Reg. Expression to replace a list of names with a single character.
I have a column that is in a string format, and of course contains names. Majority of the rows are an empty string. What I am looking for is to take the rows that do contain a name and replace them with a 1. i have been trying to use replace text tile then trying to use a regular expression in the 2nd step, then replace…
-
Can I dynamically group data to two periods (Current 12 month and Prior 12 months) ?
Can I dynamically group data to two periods (Current 12 month and Prior 12 months) using window function so I can select time periods to compare. Client wants to be able to select months in the past and compare rolling 12 month as of the last date select date. I tried this formula for Current period, but it doesn't…
-
Changing a value to an integer in the ETL
I have a column that uses an 'x' as a flag, any row that isn't flagged with an 'x' is left as an empty string. What I need is to replace the 'x' with a '1' to be used in a later count. this should be straightforward but I keep getting my wires crossed with the order of operations.
-
Previous day values - Following Day Values Restart Calculation Each Month
Hey guys, I have a dataset that appends each day to include the daily downloads, and resets the calculation on the 1st of every month. This issue is, it's cumulative totals, so if yesterday we had 40 downloads and the following day we had 15 downloads, the total for the following day would come in as 55 total downloads.…
-
Assembler Upsert Date Column
Hi. I need to take a snapshot of today's data in order to compare it with tomorrow's data once it will be available. The problem is that the SQL dataflow and Magic ETL only can use Replace method. I need to find a way how to append the data each day to the already existing historical table. I created Assembler and I chose…
-
I need help creating a retention chart
I am trying to do an analysis to show users who came back to make 100 api calls after the making their first 100 api calls. For more clearity, "100 api calls" is a key activation metric for us. The way I plan going through this is to use a line + grouped bar chart where I'll have the 100th api call date as the X axis and…
-
Where can I find the logic for custom built dimensions?
Hello, I'm having an issue tracking down where, exactly, I can find the logic for these custom-built Dimension Segments. Any tips for where I might find it? Thanks!
-
Swapping Pivot Table Rows with Columns
Hello, I am trying to create a table where the columns are a categorical variable and the rows are a quantitative variable, such that the intersection of each row and column will provide the relevant quantitative value for each categorical value, however I am finding that I am only able to create any sort of chart that…
-
Time Format
Hello, I am combining date and time in one column but once is combined the date format is ok but my time format is all zeros. is there something I am missing? Thank you!
-
Join Issue
Good afternoon! I am having a join issue where only my inner join is working, but I would like to either a left outer join or a full outer join. I am trying to join 417 rows of data to 15 rows of data. Thank you!
-
Date Diff - Tenure
Hello, I am looking to build a tenure-based view for a list of employees. I have a hire date field and I want to create a filter to show some categorized options. Can someone validate if this is possible and any ideas will appreciated Employees that have 3 weeks of experience ( Something like if datediff today()- HireDate)…
-
How can I split 1 column into multiple columns
Hi, Is it possible to split 1 column to multiple columns by specifying the splitting character? For Eg, I have column named Campaign and the values in the column follow a standard format like 'Campaign Date_Campaign Name_Campaign Type_Source_Language_Status' Is there a way to split this column by '_' ? Thanks, Angel
-
Redshift Unable to create database.
We are getting the below error when trying to build a Redshift dataflow: We are unable to run previews, and we are unable to run the dataflow itself. It fails every time we try to run. Any idea what is causing this issue?
-
Beast mode with multiple case statements
Hi, I would really appreciate help with a beast mode that I am unable to resolve even after looking into many answers on dojo. The beast mode looks like below: CASE when (CASE when `Cost Type` like 'Fix Price' then (CASE when `End Date` < CURRENT_DATE-3 then `CPM` else (`CPM`/(DATEDIFF(`End Date`,`Start…
-
Getting a Sum of Values for X Days When There are Gaps in the Dates
We have our dataset of transactions for a large number of SKUs. Dataset is over 1 Million rows. We want to examine the date of each transaction and go back 30 days to get a 30 day average of sales for each SKU based on the date of that record. Normally I would be thinking Row and Window tile in Magic ETL. But here's the…