2 Replies Latest reply on Apr 7, 2017 1:24 AM by SANDIP SHARMA

# How to compute percent change field for values that are computed using conditional logic around a date parameter

Hi all-

In the attached worksheet, I've set a date parameter that allows the user to focus on a specific week.  Rather than filter the sheet, I calculate each field relative to the date parameter.  My cost field for the current week is calculated as IF [Date]=[Week Select] THEN [Cost] END and the cost field for the previous week is calculated as IF [Date]=[Week Select] - 7 THEN [Cost] END, etc...

After calculating a rate field for the current and previous weeks, I'd like to show the percent change in rate week over week.  It seems like I can't calculate a field based on conditional logic that points to two different dates.

The entries in my data don't appear consistently each week, so a LOOKUP() may not work.  In the table calc menu, the option for computing across the table isn't available.

I'd like to keep the date parameter functionality, but I'm willing to change the format of my data if necessary.

Thank you.

• ###### 1. Re: How to compute percent change field for values that are computed using conditional logic around a date parameter

Hi Michael,

Do you still need assistance with this? One option may be to create additional calculated fields that find the difference between the fields, i.e.:

sum([Cost This Week])-sum([Cost Last Week])

-Tracy

• ###### 2. Re: How to compute percent change field for values that are computed using conditional logic around a date parameter

Hi Michael,

I believe you are looking for below output.

([Cost per Metric This Week]-[Cost per Metric Last Week])/[Cost per Metric Last Week]

Corrected Formula:

(sum([Cost per Metric This Week])-sum([Cost per Metric Last Week]))

/sum([Cost per Metric Last Week]) 1 of 1 people found this helpful