1 2 Previous Next 15 Replies Latest reply on Aug 15, 2016 8:42 AM by Andy Cook

    Median Filter

    Andy Cook

      Hi everyone, I have what I think is a relatively simple problem. I created a model that I use to compute the median employee based on total cash compensation (a separate calculation). I created the calculation below that returns in a cross tab several pieces of information for the identified employee(s). 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. Can I modify the calc below (I use this as a filter) to return employees in both scenarios?

       

      IF ROUND([Total Cash Comp (USD)],0) = ROUND([Window Median],0) THEN 'Median' ELSE 'Not Median' END

       

      *"Window Median" is the following calc:

      WINDOW_MEDIAN([Total Cash Comp (USD)]) - Results computed along table across

       

      Thanks, Andy

        • 1. Re: Median Filter

          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!

           

          Andy Cook

          • 2. Re: Median Filter
            Mark Fraser

            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

              Thank you both very much.

               

              Andy

              • 4. Re: Median Filter
                Mark Fraser

                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

                  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

                    Hi 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

                    1 of 1 people found this helpful
                    • 7. Re: Median Filter
                      Andy Cook

                      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

                        Thanks Mark...and no-pressure 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!

                        1 of 1 people found this helpful
                        • 9. Re: Median Filter
                          Yuriy Fal

                          Hi 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 Row-Level 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

                          2 of 2 people found this helpful
                          • 10. Re: Median Filter
                            Simon Runc

                            very 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!!

                            1 of 1 people found this helpful
                            • 11. Re: Median Filter
                              Andy Cook

                              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

                                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

                                  I'm still 50/50 in TC16 (...in delicate negotiations with the boss)

                                   

                                  ...but if I'm successful, I'll (Oscar-style) accept the 2nd beer on Yuri's behalf

                                  • 14. Re: Median Filter
                                    Andy Cook

                                    Hi Yuri, I'm working through your solution and so far, so good! I want to thank you again for your guidance.

                                     

                                    Andy

                                    1 2 Previous Next