3 Replies Latest reply on Sep 22, 2017 9:00 PM by Shinichiro Murakami

    How to calculate 3 Year Median and make it fixed irrespective of the date filter

    Naveen Ambati

      Hi ALL,


      Before I go about putting out my problem, I am very thankful to all the folks who take the time to answer and provide solutions to all my Tableau related problems.


      I have a requirement in one of the dashboard to calculate the 3 Year Median.


      The 3 Year Median is the Median of # of days taken by projects for the last 3 years(current - 3 years = 2014,2015,2016).


      Once the above calculation is made I have to get the Diff from 3 Year Median (Median Duration - 3 Year Median)


      In this case the Median Duration is the Median of # of days taken by projects based on the filter criteria.


      Apart from the 3 Median calculation I am running into other problem with the relative date filter. When the user selects "This Year" 2017 in the date filter the 3 Median becomes Zero. So the 3 Median should reflect other dimension filters but be immune to the date filter.


      I have a hunch that using LOD might give me the solution, but don't have the expertise in setting up. I am not sure if a Window function will work, since I don't have Year in the Sheet


      I am also attaching a sample workbook with some sample data.


      Thank you so very much for all the time and effort