-
Can I move a formula in the list of "Group by" actions?
I've wondered this for a long time, but I'm always in a rush and end up using a select columns tile. That's inefficient, so I thought I'd ask … Here's the scenario: I've created a bunch of columns in my "group by" tile that spit out columns. I realize later that I used a basic "add column" function when I really needed a…
-
Summing by groups in beast mode?
I'm running on 2 hours of sleep so I think I am just overthinking this right now but I essentially have a large dataset of event attendance by members Here is a sample of my data: Event Date Event Name Member ID Member is Registered for Event Parent Group Location Type Apr-25 Event 1 1 0 Primary Primary Apr-25 Event 1 2 1…
-
Group By tile not working with large dataset
Hi, I have a dataset of about 4.7M rows. When I try to use the Group By tile in Magic ETL to find a sum of values across IDs, and then join back on the ID, the new sum column is blank. However, in testing, when I filtered the dataset down to about 50K rows to see what was wrong, the example ID I was checking now had the…
-
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…
-
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…
-
Group By Issue
I need assistance with identifying transactions that net out to zero in a large dataset. Each row in the dataset represents a unique transaction with its own ID. Objective: Identify pairs of transactions that meet the following conditions: Same client ID Same transaction type ID Same transaction date The sum of their…
-
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…
-
Grouping dates of service to match a pay period
I have two data sets one with employee pay information that is bi-weekly. And one that has information about the employee's work services they provided The one with employee work data has dates that show when the service was performed (Date of service) and how many hours it took to do the service (Billable hours). What I…
-
Magic ETL - how to structure Multiple fields that represent monlthy budget amounts
Hi i have a question concerning structuring variable fields using group by. So currently I have 12 variable fields, where a user can input a budget amount in each field, for a total of 12 months. the budgeting can start during any given month, so i have a starting month field, that the user must use to enter the budget for…
-
Displaying 1 to many records in a table
I have created a custom app for my users to be able to add notes to Invoices that are in collections. The relationship is 1 Invoice to many notes. Initially, my plan was to display the Invoice details in a table card ( Customer, Invoice #, Contact, Invoice Date, Invoiced Amount, Total Due, etc.) and the note data would…
-
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…
-
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…
-
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…
-
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…