2 Replies Latest reply on Jul 14, 2017 10:46 AM by Jim Dehner

# Find Median % Diff based off calc field applied to IF THEN Statements

I created an IF THEN measure to create a date filter for the same amount of time over the years. I then calculated the median value for that time. Now, I want to compare the two medians and find % Difference. I realize this is hard to understand, but here's an example:

• Filtered Data to show Nov 1-7, 2016 and Nov 1-7, 2015 (Assume today's date is November 7)
• IF [Date]< TODAY ()-365
• AND [Date]>TODAY()-372
• THEN "Last Yr"
• ELSEIF  [Date]<TODAY()
• AND [Date]TODAY()-7
• THEN "Current Yr"
• ELSE "False" END
• Created Calculated Field to calculate median of live data
• WINDOW_MEDIAN(COUNT(Sale ID))
• ^^The above steps creates a view showing all the dates and the median for each time period. How can I compare the percent differences? Given:
• I don't have any of the quick table functions.
• Analysis tab is not helpful

Any and all help would be very much appreciated!

• ###### 1. Re: Find Median % Diff based off calc field applied to IF THEN Statements

Please attach a mock up workbook.

• ###### 2. Re: Find Median % Diff based off calc field applied to IF THEN Statements

Hi Its a little difficult without a workbook as Deepak noted

but have you tried a lookup function on the calculation you used to determine the median

it you would be of the form % growth =     ( (lookup(median formula),-1) -( Medain formula for the period))/(lookup(median formula),-1)

You may have to drop in the actual for the median formula -

• WINDOW_MEDIAN(COUNT(Sale ID))

Let me know if that helped

Jim