Determine % difference for values between two different dates

[Philip Stonecipher]

I'm trying to figure out what expression to write to determine the decline % for values between two dates. For example in the following table, I want to take the value on 12/1/2021 and determine the difference to the value on 1/1/2021. In this example it would be (100-45/100) or 55%. I'm a rather novice with Spotfire and can't figure out what expression to write to get Spotfireto do this. Any help is greatly appreciated!

 

 

 

Date

Rate

 

 

1/1/2021

100

 

 

2/1/2021

95

 

 

3/1/2021

90

 

 

4/1/2021

85

 

 

5/1/2021

80

 

 

6/1/2021

75

 

 

7/1/2021

70

 

 

8/1/2021

65

 

 

9/1/2021

60

 

 

10/1/2021

55

 

 

11/1/2021

50

 

 

12/1/2021

45

[Fabian Duerr]

Please start with a little helping column. For example;

First(If([Date] = Date(2021, 01, 01), [Rate], NULL))This will create a column with all values being the rate of 1/1/2021. Everything else is then straight forward.

Later on you might want to have the reference date selection more flexible. You can use a document property to achieve this. For example a drop-down list with all unique values from your [Date] column.

Or maybe you always want to compare to the rate of the smalllest date.Then you would simply use Min[Date] instead ofDate(2021, 01, 01)