Magic ETL - Ignoring multiple Daily average amounts that display on the same dates

Gojo

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.

david_cunningham

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).

GrantSmith

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

Gojo

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.