I recently started creating reports in tableau and I am stuck at this point . I am dealing with some faculty data here . I have data for salary for all kinds of faculty coming in from Custom Sql query( oracle db having about 6 tables joined) and I have the position Title, specialty ( ex: cardiology, Dermatology) adn Degree( MD, Bachelors etc) coming from the tableau server. The common column in both the datasources is the Emp_ID. I tried to blend the data keeping the Custom Sql query as the primary datasource and blended it with the secondary data source that is the tableau server . I am trying to achieve a mean, median, 25th percentile,75th percentile for this data for all the list of faculty within a specialty , who have done a MD or PHD by Degree and by title( professor, instructor, asst professor etc) .
So I have taken the distinct count by employee , did the avg( emp_total_salary )/ NO OF employees for Mean , Median as the measure function a Measure --> Median , 25th percentile as Measure --> percentile --> 25 ,75th percentile as Measure --> percentile --> 75.
I do get those results but the values are not what you calculate manually in Excel. The mean, median and the percentiles amounts show far less than what it needs to be.
For example there are 5 employees with the title professors in the specialty of Cardiology who have done their MD only as Degree , and their salary is 100,200,300,400,500 respectively, the mean should be (100+200+300+400+500)/5 = 300 and the median should be 300 , 25th percentile should be =PERCENTILE(C6:C10,0.25) = 200 and 75th percentile should be =PERCENTILE(C6:C10,0.75) = 400. The above calculations are in Excel but as per what i am looking at in Tableau , the calculations are going way off and i am unsure where to apply any filters or add parameters or i just don't know. When the see the underlying data, i see all the list of professors across all specialties . and even if i consider that, their average/ median doesn't match either .
And the data consists of all specialties all titles and all degrees . I need data avg by specialty /degree/title to create a benchmarking as to who falls below and above 25th and 75th percentiles respectively.
A colleague of mine told me i could use Cross joins between the tableau server extract and the Custom sql extract. I tried to search some information online but it says we cant join a tableau server with a custom sql query. When i tried to join the tableau server to the Custom sql query the tableau server option greyed out.
I Need the data from both the data sources and one is definitely dependant on other. This is on version 2018.1
Any inputs will be appreciated !
Thanks in advance,