Median Date
Hello,
I have 2 columns One has different markets like A, B and C. Each market has some dates.
The scenario is if I pick A there is a range of dates with a minimum date, maximum date. I want to create a new date column which will populate median of the above date range for A in it. Obviously, the median will be one date populated against all rows for A, similarly for B and C.
Also, dates can be repeating  For example: 6 December 2016 can appear 4 times, 25 December 2017 can appear twice. These are not duplicates but they are different by lets say a size of that product.
I am seeing a median date in the Data section, but I am not understanding how it can be created in the ETL as, it won't allow to get a Median function in Group By for dates.
Thanks in advance!!
Best Answers

This is a fun one! Here's how I might find the median date in ETL:
I'd use a row number function, partitioning by market and sorting by date, to get the data in order and count the data rowbyrow.
I'd also have a separate group by, by market, where I'm getting a total count of records and dividing by two.
Next, I'd join that total/2 number to the row number tile on the market field. Wherever the row number equals the total/2 number, that should be the median. I'd use a filter to isolate that date, and then join it back to the main data in the ETL, again on the market field, so that every record would have the median date populated.
Hope there's an easier way to do it, but here's one way at least!0 
Another potential alternative is to convert your dates into unix timestamp (TO_TIMESTAMP) which would be a number, this would then allow you to run a median on the number and then just convert the median back to a date (FROM_UNIXTIME)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers

This is a fun one! Here's how I might find the median date in ETL:
I'd use a row number function, partitioning by market and sorting by date, to get the data in order and count the data rowbyrow.
I'd also have a separate group by, by market, where I'm getting a total count of records and dividing by two.
Next, I'd join that total/2 number to the row number tile on the market field. Wherever the row number equals the total/2 number, that should be the median. I'd use a filter to isolate that date, and then join it back to the main data in the ETL, again on the market field, so that every record would have the median date populated.
Hope there's an easier way to do it, but here's one way at least!0 
Hi Sean,
Thanks much for replying. I will give this a try 🙂
0 
No problem, and good luck!
I haven't had to find a median date previously, so I think there are some details I didn't think of, like the total being an odd number, and therefore the total/2 not being whole. Not sure what the best way to handle that would be  my first thought was rounding, but I'm not sure if that would get the right result every time.
0 
Noted! Thanks!
0 
Another potential alternative is to convert your dates into unix timestamp (TO_TIMESTAMP) which would be a number, this would then allow you to run a median on the number and then just convert the median back to a date (FROM_UNIXTIME)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2 
@GrantSmith, this worked. I didn’t even have to deal with duplicate dates and the same results as we see in Data section.
Supperb!! Thank you both @Sean_Tully and @GrantSmith for jumping in to help!!
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 565 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 593 Beast Mode
 13 App Studio
 28 Variables
 588 Automate
 143 Apps
 417 APIs & Domo Developer
 27 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 361 Distribute
 99 Domo Everywhere
 260 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive