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

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

Regards,

Naveen

• ###### 1. Re: How to calculate 3 Year Median and make it fixed irrespective of the date filter

Hi Naveen,

Below two fields show exact same formula, but to differentiate the range, need to prepare two.

Remove duplicated items with filter (step2)

If you want to show relative last year with filter, you need to change all the filters to context to make LOd work.

If you want use absolute three latest years nevertheless of filtered item, you don't need to change to context.

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

• ###### 2. Re: How to calculate 3 Year Median and make it fixed irrespective of the date filter

Hi Shin,

Thanks a lot for your response and explanation. Sorry I am a bit confused and probably didn't explain my self well.

• Process: Projects that have the completed the following events (PP and APPR)
• # of Projects: # of Projects that have completed the above Process. It is the count of Distinct projects.
• Median Duration: It is the Median # of days taken between those two events
• Difference from 3 Year Median: It is difference from above Median duration.(Median duration - 3 Yr Median)

To calculate 3 Year Median(take all the projects which have End date in 2014,2015 and 2016) and calculate the median.

My end output should look like the screenshot below. If I have projects in the window(detail card) how will I be able to summarize the numbers into single row.

The 3 Year Median should always be calculated for 2014,2015 and 2016 only.

But if the user filters for a specific country and for 2017 only the Median Duration should be for that Country and 2017 where as 3 year median should be for the 3 years(2014,2015 and 2016) for that country.

I hope I explained my problem a little better. Thanks so very much for your time

Regards,

Naveen

• ###### 3. Re: How to calculate 3 Year Median and make it fixed irrespective of the date filter

Hi Naveen,

To show only the latest year, add one more index filter.

To calculated the LOD only in the range of filtered dimensions, change the Date filters to context.

Thanks,

Shin