
1. Re: Median Filter
lenaic.ridinger Aug 5, 2016 4:16 AM (in response to Andy Cook)Hey Mark Fraser,
I was wondering if this is something you could help me with?
If not, my apologies for popping you in; If yes, wonderful!

2. Re: Median Filter
Mark Fraser Aug 5, 2016 6:39 AM (in response to lenaic.ridinger)Hi Lenny
I will try and have a look for you, but I'm up to my eyeballs at the moment with some App Store submissions (I'm also an occasional iOS/Android developer) so it won't likely be until Monday...Apologies
Mark Fraser pinging myself as a reminder
Cheers
Mark

3. Re: Median Filter
Andy Cook Aug 5, 2016 7:14 AM (in response to Mark Fraser)Thank you both very much.
Andy

4. Re: Median Filter
Mark Fraser Aug 9, 2016 2:52 AM (in response to Andy Cook)Hi Andy
Apologies, bit delayed looking at this... My suspicion is that its the way Tableau is calculating the median (vs your expectations)
(you have a lot going on in your question, so any samples you could provide would help, a lot!!)
But for now... I have been playing around with medians and you do have to be careful with Medians
Example:
From here >> How to Find the Median Value
Now in Tableau
Apparently (and I need to do some more work on this) Tableau sees this list 3 5 7 12 13 14 21 23 29 40 56
And this is why the median comes out as 14
But bringing in the number of records, we know that actually are list has more values (the repeating 23) which aren't being taken care of...
With an odd length list its fine...
Personally i would have a look at creating the 'median' myself first, so I can be sure of the calculations rather than using the inbuilt calcs
There maybe something useful in this thread Means, Medians, and Modes...oh my
and/or Calculating the median based on the group
If you could provide any sample it would certainly help!
Cheers
Mark

5. Re: Median Filter
Andy Cook Aug 9, 2016 7:26 AM (in response to Mark Fraser)Thanks very much Mark. I've created a sample workbook for your review. My logic falls apart (does not return employee(s)) when there are 2 median employees with different salaries/total cash comp. You will see this when you manipulate the filters.
Regards, Andy

6. Re: Median Filter
Mark Fraser Aug 11, 2016 1:52 AM (in response to Andy Cook)1 of 1 people found this helpfulHi Andy
Apologies for the delayed reply, I'm on holiday next week and a little busier than normal
I have had a (admittedly brief) look at the workbook and I think the issue is actually described here...
However, this only works if there is a single median employee or multiple median employees with exactly the same cash comp. If the calc returns more than 1 median employee who have different cash comp calcs, the cross tab returns no one.
What (I think) happens 
It is working when you select individuals (or like for like values) because its effectively saying  do I equal myself? and the answer is yes, so it returns.
But when you add more employees  you amend the value of WINDOW_MEDIAN value to a value which isn't now present, and try and test against it (as an exact match) < that won't work, as you have found.
I will try and explain with examples:
Emp 1 USD = 1 Median is therefore 1 1=1 is OK
Emp 3 USD = 5
Emp 4 USD = 5 Median is 5 5=5 is OK
But you add in a 2nd non match, this means a variation of the median is calculated, and therefore no longer matches (i.e. no longer satisfies the Median/ Not median test)
Emp 5 USD = 8
Emp 6 USD = 10 Median (should be) 9 8 /= 9, 10/= 9, nothing is returned.
I think what you'll need to do, is to revise the below to include some kind of range
IF ROUND([Total Cash Comp (USD)],0) = ROUND([Window Median],0) THEN 'Median' ELSE 'Not Median' END
So in my example, if we said USD >= MEDIAN 1 and USD <= MEDIAN +1 then it would have satisfied both results and returned.
I hope the above makes some kind of sense, and I hope I have correctly identified the issue, currently it seems to make sense to me, but I'm conscious I could be completely wrong!!
As mentioned, I'm away next week, if you need a hand, I would suggest Simon Runc, that guy is SMART, and extremely nice & helpful!
Hope that gives you a start, I'm off for a lie down
Cheers
Mark

7. Re: Median Filter
Andy Cook Aug 12, 2016 7:08 AM (in response to Mark Fraser)Thanks again Mark. You're spot on with the details of my problem and I think your solution is a good start. I'm testing the following formula:
IF SUM([Annual Base Amount (USD)]) >= [Window Median  Ann Base] 1
AND SUM([Annual Base Amount (USD)]) <= [Window Median  Ann Base] +1
THEN 'Median' ELSE 'Not Median' END
However, the way I've applied (probably incorrectly) it adds or subtracts 1 from the result of the window median and returns more than 1 result. My thought was that we want the "+1+ or "1" to return the values that are up 1 and down 1 within the median. Basically, I don't want the median to behave the way it's supposed to (haha) but instead return the 2 different median values that Tableau averages.

8. Re: Median Filter
Simon Runc Aug 12, 2016 8:25 AM (in response to Andy Cook)1 of 1 people found this helpfulThanks Mark...and nopressure on being SMART enough to find a solution!!
hi Andy,
So I've only had a quick look at this, and only have a few mins now...I'll can go into more detail when I get sometime tomorrow (let me know, if you need this).
So we won't be able to change the way tableau (or any other software) would return the median value (...in that it is correct!). As I've noticed that you have [Emp ID] in your table, it gave me an idea!...we could get to the Emp ID you need by using RANK and COUNTD to determine the middle 1 (if odd COUNTD) and the middle 2 if it's even.
The following calcs are a little complicated, but hopefully the Table I've built on 'SR' tab, helps explain...
So first I rank the [Emp ID] on 'Base Ann'
[Rank  Ann Base  SR]
RANK_UNIQUE(SUM([Annual Base Amount (USD)]),'desc')
Then I get a COUNTD of all the [Emp ID] that are in the view (i.e. after filtering)
[Number of Emp ID Selected  SR]
WINDOW_SUM(COUNTD([Emp ID]))
and then we use these 2 values to determine in the [Number of Emp ID Selected  SR] is odd or even (that's the %2 part...this is the Modular function)...and then we can match the RANK to return the correct [Emp ID](s). In my example I've marked the ones to return with a 1
[Return Emp ID  SR]
IF [Number of Emp ID Selected  SR]%2 > 0 THEN
IF [Rank  Ann Base  SR] = ([Number of Emp ID Selected  SR]/2)+0.5 THEN 1 END
ELSEIF
[Rank  Ann Base  SR] = ([Number of Emp ID Selected  SR]/2)+1 OR
[Rank  Ann Base  SR] = ([Number of Emp ID Selected  SR]/2) THEN 1
END
Hope that, sort of, makes sense. Let me know if you think that'll work for you, and if not why...and also let me know if this works, but doesn't make any sense (and I'll give a more detailed explanation)
...nice little challenge this!

9. Re: Median Filter
Yuriy Fal Aug 12, 2016 2:53 PM (in response to Andy Cook)2 of 2 people found this helpfulHi Andy,
You may want to use a "regular" aggregate MEDIAN() calculation.
For that you would not be using Table Calculations, but  given [Emp ID] 
opt to using RowLevel and FIXED LODs (before aggregates) instead.
It means that you'd be using your Filters mainly as Context ones.
Please find the attached w/Sheet 7 as an example.
Hope it could help.
Yours,
Yuri

10. Re: Median Filter
Simon Runc Aug 13, 2016 2:24 AM (in response to Yuriy Fal)1 of 1 people found this helpfulvery nice Yuri (as always!)...that took me a good 20 mins to work out how it did it!!
I might still be in the dark, had it not been for Tableau's (very useful) drag part of a formula onto the canvas...
Now that is a SMART solution!!

11. Re: Median Filter
Andy Cook Aug 14, 2016 8:40 AM (in response to Simon Runc)Simon and Yuri,
Thanks very much for your work on this. I can't wait to try your solutions! Any chance either of you will be attending the Tableau Conference this year? If so, I owe you both a pint.
Regards,
Andy

12. Re: Median Filter
Yuriy Fal Aug 14, 2016 8:51 AM (in response to Andy Cook)Andy, you're welcome.
Pity I would not attend the TC16,
so you owe two pints to Simon :)
Yours,
Yuri

13. Re: Median Filter
Simon Runc Aug 15, 2016 12:58 AM (in response to Andy Cook)I'm still 50/50 in TC16 (...in delicate negotiations with the boss)
...but if I'm successful, I'll (Oscarstyle) accept the 2nd beer on Yuri's behalf

14. Re: Median Filter
Andy Cook Aug 15, 2016 6:22 AM (in response to Yuriy Fal)Hi Yuri, I'm working through your solution and so far, so good! I want to thank you again for your guidance.
Andy