Hello,
I need to calculate median using a live connection (I can not use an extract)
I'm attempting to do a window function and not getting the correct output. Also, I don't want MAX of playcount, but I can't do a window function without an aggregate.
I made this version using an extract. The below is what I want, but I am unable to use an extract on an ongoing basis.
The below is what I get using my incorrect window function.
WINDOW_MEDIAN(MAX([play_count]), FIRST(), last())
Any help would be appreciated!
Just to chime in here @Madeline Kornack & @Ayinde Hammed, Median is an extract only calculation, Tableau requires an extract in order to calculate the median, though this isn't to say that medians can't be calculated.
The reason you are seeing a single straight line across your data Madeline is that there isn't enough information in your defined window for Tableau to to create medians from, essentially you are seeing the median for the whole partition, rather than the median for each bin.
The same can be seen here in Superstore - the top line is the Window Median:
And here is the same set of values in Excel:
But lowering the grain of my data to day, and then adjusting the scope of the Window_calc to day, we can see that the window median has adjusted in turn:
You can see from the above image that the median value is 2694 for January, this is the 19th value in the set when ordered by date, but is actually the 16th number when ordered by value: 31 days in January, n= (31+1)/2 = 16
So you have a few options available to you:
-/ You can manually calculate median with the formula (n + 1) / 2 where n = the count of the numbers in your set - though this can become quite complicated as you need to count the numbers of periods in your set and then use this number to identify the number sitting on this "row" once your set is sorted as I have done so above.
-/ Use the window_median method you are using right now, though you will need more information in your partition in order to find the median, and you'll need to use late filtering to then remove the grain data to leave you with the final number:
You can see from this image that I am connected live to SQL Server here, the median calc I am using here is the same as yours:
Window_Median(Sum(Sales))
Scoped to Day of Order_Date
-/ Consider extracting your data into a secondary data set just for this one calculation - you could probably make it lightweight
-/ Consider a pass-through calc to Python or R providing you have those servers set-up and your Tableau server can connect to either TabPy or an r-serve instance.
Assuming you cannot use a pass-through calc, neither can you use an extract for this one piece, Custom SQL is out of the question enabling the server to handle the calculation, then I'd be using table calcs like you with late-filtering as in my example above.
Steve
