
1. Re: ACS DataSPSS & Tableau Generating Different Medians
Jonathan Drummey May 23, 2017 12:39 PM (in response to Carolyn Fuqua)Hi Carolyn,
Can you share a packaged workbook with some sample data and what you're expecting to get?
Where I most commonly see this kind of problem is due to the level of calculation/order of operations. For example if the weighting is performed after aggregation in Tableau you're likely to get a different result than if the weighting is performed before aggregation.
Jonathan

2. Re: ACS DataSPSS & Tableau Generating Different Medians
Carolyn Fuqua May 24, 2017 7:54 AM (in response to Jonathan Drummey)So good of you to respond, Jonathan! I've been more than a little stumped by this. I've attached the workbook. My apologiesit's a rather large dataset. All I'm trying to do is generate a median earnings estimate for holders of terminal bachelor's degrees in the humanities with fulltime employment. You'll see that I've used three sets to identify the subpop on which I want to perform the calculation. To accomplish the weighting, I've created a calculated field "Weighted Earnings". To get the IQR I just used the canned "Median with Quartiles" routine on the Analytics sidebar.
I've attached the workbook. My apologiesit's a rather large dataset. All I'm trying to do is generate a median earnings estimate for holders of terminal bachelor's degrees in the humanities with fulltime employment. You'll see that I've used three sets to identify the subpop on which I want to perform the calculation. To accomplish the weighting, I've created a calculated field called "Weighted Earnings". To get the IQR, I just used the canned "Median with Quartiles" routine on the Analytics sidebar.
The median Stata (not SPSS, sorry) yields is $52,065 and change. You'll notice that what Tableau gives me is roughly a thousand dollars more.
Many thanks in advance for any light you can shed on the source the discrepancy.
Best,
C

3. Re: ACS DataSPSS & Tableau Generating Different Medians
Jonathan Drummey May 24, 2017 7:59 AM (in response to Carolyn Fuqua)Hi Carolyn, I’m not seeing the attachment?
Jonathan

4. Re: ACS DataSPSS & Tableau Generating Different Medians
Carolyn Fuqua May 24, 2017 8:47 AM (in response to Jonathan Drummey)I def attached. I wonder if it wasn't too large. Here 's a Dropbox link to the thing: https://www.dropbox.com/s/z7z0bjutn7dan8j/ACS%202015_for%20jonathan.twbx?dl=0

5. Re: ACS DataSPSS & Tableau Generating Different Medians
Jonathan Drummey May 26, 2017 11:54 AM (in response to Carolyn Fuqua)Hi Carolyn,
The original file was too large, I've created a Tableau data extract to compress the data to a manageable size. I don't have time to do a full explanation of what I've built, I've attached the packaged workbook and can show you some screenshots, here's a view with the accurate weighted median:
There are several different methods for dealing with ties for weighted medians so I built out calculations to handle all of them. The Weighted Median calculated field uses a parameter to set whether a weighted interpolation is used, the mean value, low, or high. The interpolation uses the same algorithm that R's MatrixStats weightedmedian() function uses. In addition I created a new version of the Likert scale algorithm described in this thread Re: Calculating the median of Likert scale data that assumes that the 5 point scale represents a continuous random varia…..
Note that for this view that we don't need to have Analysis>Aggregate Measures turned off, instead we can Earn2015 as a dimension in the view with a compute using for the calculation on the Earn2015 dimension and and use the High value of the parameter to get the desired result.
I'm working on a longer writeup, it'll take me until Monday or Tuesday to get it done.
Jonathan

weighted median.twbx 16.6 MB


6. Re: ACS DataSPSS & Tableau Generating Different Medians
Carolyn Fuqua May 30, 2017 6:45 AM (in response to Jonathan Drummey)My, Jonathan. This is truly above and beyond. I can't thank you enough. Will carefully review and internalize!
I hope you will let me credit you when we post our estimates in a couple of weeks. How would you like to be identified? And lease send me a link to the Tableau, Twitter, Facebook, etc., account(s) on which you'd like folks to follow you.
Gratefully,
C

7. Re: ACS DataSPSS & Tableau Generating Different Medians
Carolyn Fuqua May 30, 2017 11:14 AM (in response to Carolyn Fuqua)Aack. Having difficulty adapting what you were kind enough to send me to also produce the upper and lower quartiles that I need. Could you spare a moment to enlighten me?

8. Re: ACS DataSPSS & Tableau Generating Different Medians
Jonathan Drummey Jul 3, 2017 8:32 PM (in response to Carolyn Fuqua)1 of 1 people found this helpfulHi Carolyn,
I'm sorry for the delay in responding, I never got a notification of your second post and . Here's a link to a more detailed post I (finally) completed about computing the weighted medians:
Weighted Medians for Weighted Data in Tableau — DataBlick
As for my public info, here it is:
Twitter: http://twitter.com/jonathandrummey
LinkedIn: https://www.linkedin.com/in/jonathandrummey/
Web: http://datablick.com
I'll have to work on the weighted 25th and 75th percentile calculations...I can get them to work, it'll just take a little time to undo the optimizations I made for just medians.
Jonathan

9. Re: ACS DataSPSS & Tableau Generating Different Medians
Carolyn Fuqua Jul 5, 2017 8:42 AM (in response to Jonathan Drummey)Tremendous! Again, incredibly good of you to tackle this. Thanks for the opportunity to learn from you.

10. Re: ACS DataSPSS & Tableau Generating Different Medians
Yuriy Fal Jul 15, 2017 9:20 AM (in response to Jonathan Drummey)Hi Jonathan,
As a sidenote, I thought that as for visualizing weighted distributions,
your good old (almost 1year since :) Marimekko would be of use.
Please find the attached (version 10.0, hence Mekko, almost :).
I've used the extract datasource from your weighted median wb.
Yours,
Yuri

11. Re: ACS DataSPSS & Tableau Generating Different Medians
Jonathan Drummey Jan 24, 2018 11:39 AM (in response to Carolyn Fuqua)Hi Carolyn,
I know this is months later...are you still looking for help with the 25th and 75th percentiles?