-
Having Multiple Group By tiles?
I am looking to have two separate groupings in one ETL. Is there a way to do this without having to use two Group By tiles? Also I no longer see the minimum aggregate option for my new column. Do I now have to use the Min() formula?
-
Group By issue
I am doing a group by tile in magic etl. i have different values for different ID's and need to sum up all the values in each ID. For some reason some of the ID's are not summing and I get null in column for the ID. I did formula to make sure all null values before grouping are 0 thinking that would fix it but for some…
-
Group By Question
Im trying to do a group by to get a new column with total sum of charges by bill ID. I want the total sum to be constant all for every day associated with Bill ID. When I do a group by I am not getting the new column to sum the charges. Bill ID Date Charge 502 4/10/2023 $85 502 4/11/2023 $76 502 4/12/2023 $110 502…
-
Magic ETL - Allow group by without a grouping column
There are times when I want to calculate aggregated metrics across the entire dataset. Currently this isn't possible without adding a constant value column to group on which I then have to remove later in my pipeline. It'd be much smoother if the Group By tile allowed us to group across the entire dataset without having to…
-
Keep Columns After Using Group By Tile in ETL
I haven't played around with the group by tile yet so this might be a very simple answer I overlooked. But why do all of my columns disappear and leave only the columns I identified in the group by tile from my data after I use this function? I know I could join the data back in but I was assuming I was using it wrong.…
-
Multiple singe day events grouped as single consecutive day events
I am trying to figure out a way, in Magic ETL, to take multiple single day events and group them when there is break in consecutive days. Example, if the results show the 1st, 2nd, 3rd, 5th, 6th, 9th, 10th and 11th, I need three results, not 8. I want to see begin and end dates of 1st-3rd, 5th-6th, and 9th-11th. Any…
-
Add ORDER BY to GROUP_CONCAT Function in Magic ETL v2
Currently, Magic ETL v2 supports the aggregate function GROUP_CONCAT in its Group By tile. While clauses inside the GROUP_CONCAT function like DISTINCT and SEPARATOR are available, the ORDER BY clause doesn't work and causes the tile to fail. SQL DataFlows currently support using the GROUP_CONCAT function with the ORDER BY…
-
Group By Condition in beast mode
It would be nice to have a "Group by" condition in beast mode
-
Can I make a grouped bar chart with 2 series/categories?
Hello! I am currently trying to make a grouped bar chart that is also stacked. In this scenario I want to compare our power and core members and within those groups how many of each are a website client with us. If they are a website client, I want that bar to be stacked to show what type of website that member has. This…
-
Pivot Table to allow Sorting by Column vs. Grouping
We need a setting to switch whether columns should group the values (current behavior) or sort them when using pivot tables. By default, pivot tables will group all your Values by the columns you select which is useful when you want to see how a specific group is performing on a linear succession, like: But often you want…
-
Navigate to Specific part of dashboard page.
Option to call a URL and enable land in specific section of the dashboard withing the URL.
-
Rank Tile Not Grouping, Needs Case Sensitive box
I am using the Rank tile, ordering on a Timestamp and the column that makes up a partition is Customer. For example, I have "CUSTOMER A" and "Customer A." These are in the raw data and represent different clients, but the Rank is treating them as the same. I will probably value map one of variations to be unique, and I'll…
-
Column created with Beast mode with numeric and porcentage format
.Dear community, this month I have receipt separately two cases with the same concern: The users have shown to me functions created with beast mode, inside a pivot table card. The goal is set in the same column figure with number format and percentage, including the symbol “%”. I have attempted so many ways, but I don´t…
-
Is the data restricted for drill downs?
I am building a drill-down but seem not to have the same options as I would building a separate card. Example: I have a stacked bar overview where I am demonstrating the cutomer base by volume over months and by customer types. I am trying to create a drill down where I am showing in a pivot table view where I want to show…
-
Certification with Group Ownership
Does anyone else notice an issue when you request something to be certified and it is owned by a group? I requested a dataset be certified and I am in the group that owns it. When I go to the certification center it shows I submitted the certification but gives me an error and does not give me the option to choose which…
-
Filtering Records Based on Aggregate Criteria
I've been trying to do the following but can't figure out the best way to do it. I have a table that looks basically like this: user_id|ticket_id|inquiry_type I want to filter out rows for a specific user_id when ALL of the rows for that user_id have a value of "false_inquiry" in the inquiry_type field. But if the user has…
-
Top Sales Week by Closer
The objective is to get the closer's best week of sales without creating an ETL as we want this to be dynamic with the date range selected on the card. We have a list of sales (each have a unique opportunity Id) with project sale dates and closer names. I had thought this formula may work but I'm having issues: RANK() over…
-
Counting rows with a specific numerical value when using 'Group By'
Hi there - I'm new to Domo so apologies if this has been asked before and I have been unable to find the answer. I am using the "Group By' function in my ETL and in one of my aggregate columns I want to show the count of all the rows that have the numerical value of 80. I have tried the formula COUNT(`ptstatus`= '80') and…
-
Combining Rows of Data with a common attribute
Hi, I have a dataset which is product data cross multiple years. In some cases the same buyer bought the product in year 2022 and year 2021 and I want to roll this up into 1 line. How do I do this automatically, matching on the word 'Buyer C' (because it's not feasible to manually match them with the size of the data) Any…
-
Trying to condense / group rows to remove nulls, any help appreciated
Hello, I am working on a data flow that needs to perform various substring functions to isolate data from a large xml string. I have gotten it close, where the data is linked to the log / time / user in in the sql database, and i am able to separate values into appropriately named columns. I would like to know if/how to…
-
Is this doable? Seeing percent of total day sales by hour using Timestamp column.
Hello! I have a problem that needs to be visualized and it was phrased to me like this: I need to see what percentage of sales each hour had for the total day last year then I need that percentage multiplied by the daily goal to get an hourly goal for today. So we have this chart right now: this show us net sales today,…
-
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…
-
use fields from different tables group by date to calculate percentage
Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation. Table a looks like this: Date All Revenue 09/01/21 100 09/02/21 200 Table b looks like this: Date HDM Revenue 09/01/21 90 09/02/21 170 I need to build a table that would calculate the percentage of HDM Revenue,…
-
Creating a separate column in transforming data using magic ETL
I am using formula function to add a column that gives out values based on certain condition. I am using case condition for validating the conditions, and I want it to be grouped by Req Id field. because req Id field contains multiple req Ids and have duplication. Idea behind grouping by is that if any of that condition is…
-
Beast mode - Age buckets / grouping
Hello everyone, I am having trouble with a beast mode grouping outstanding records by age bucket. I am looking to calculate the number of days a record has open and then place the record within a range. Below you will find my existing beast mode which is an "Invalid formula". If someone could assist with the formula…
-
Add group by as an additional field
Hi everyone, I am looking to add a column to a dataset where I join all my users and their orders (could be 1 or more) and get a count of how many orders an individual user has placed. I pulled all the columns I want in my ETL dataflow, and then calculated this field using the "Group By" transformation. I group by…
-
Group By value with Average aggregation calculating differently
Hi there, I believe I'm missing what is going on with my various attempts to Group By source data in order to gain an Average aggregate of a score. I take the entire dataset and when I place it in a table card and AVG the score, the calculation is correct but anytime I use the Magic ETL I was presented with a different…
-
Calculating and Grouping Generations / Age Stratification in Domo
Hi all - I figured since I got Domo Support's help on calculating generations / age stratigification I would share the beast mode calculation for anyone else that would find this useful and not have to reinvent the wheel. CASE WHEN `dateOfBirth` >= '1946-01-01' AND `dateOfBirth` <= '1964-12-31' THEN 'Baby Boomer' WHEN…