Magic ETL - Ignoring multiple Daily average amounts that display on the same dates
Hi, I have a question concerning how to fix an issue I am having when attempting to sum daily averages. Here is the issue that I have. As you can see in the image below. I have dates, IDs, and a daily average amount, set up in a dataset in my etl. In this sample, there are a total of 2 distinct IDs, the way I would like this to work, is that each unique Id's daily average is summed for each date, so that I can see the sum of the amount spent over a range of dates. However, the issue is that as you can see in this sample image, the IDs/daily averages appear mulitple times for each date., so the actual sum of the daily averages for either of the ids are skewed. My question is, there a way for me in magic etl to only have the daily average for each ID, only count once each date, and ignore instances, where the daily average is listed mulltiple times. This will allow me to use the sum funtion when setting up cards, etc.
Any help would be greatly apprectiated. Also, I can not remove my remove duplicates, because, although the IDs are repeated multiple times for a given date, the data in each row is unique, so I need all the rows as they are.
Answers
-
This is not in magicETL, but the way your example data is set up you could sum in the card using SUM and DISTINCT with a fixed function directly on your card.
SUM(DISTINCT daily_average) FIXED (BY ID, date)
By the way, this might just be because you're showing example data. But are you trying to get the daily average or the average by id across all dates? Because right now your example data is the average by id across all dates (the average for each id is the same across every day shown).
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
If you're pre-aggregating your data in the Magic ETL and you just want to remove the duplicate records after aggregation you can use the Remove Duplicates tile keying off of the date, ID and average
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thank you David and Grant.
David, to answer your question:
——-By the way, this might just be because you're showing example data. But are you trying to get the daily average or the average by id across all dates? Because right now your example data is the average by id across all dates (the average for each id is the same across every day shown).—
I had previously tried the distinct function, however, it did not accurately sum the daily averages across all dates. Yes in the sample and in the full data, the average per day for each ID stays the same across all dates. I created the average from the date difference, number, and dividing the total deal amount by the number of days. However, as in my sample, the ids may apear on the date multiple times, so this causes the issue, because i can simply no longer sum the daily the amount of the daily averages over any given date range, because, the multiple instances, skews the numbers. Is there a way in Magic ETL, that I can each id and assign it the avg amount, for each date? or perhaps calculate the count of the number of times an id appears ona given date, and Daily average by the number of times it appears on a given date? The latter is probably overly complicated.
Hi Grant,
If i understand what you suggested, I can not use remove duplicates, because, although the IDs are repeated multiple times for a given date, the data in each row is unique, so I need all the rows as they are
Thank you both again for the assistance with this issue.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive