I have a data set that contains various quantities for several years in a Spotfire table, however there are rows defined in a column as Budget or Actual, example table below (but in reality I have multiple different columns for Value):
| Year | Value | Type |
|---|---|---|
| 2022 | 1000 | Budget |
| 2022 | 900 | Actual |
| 2023 | 1200 | Budget |
| 2023 | 950 | Actual |
| 2024 | 1400 | Budget |
| 2025 | 1600 | Budget |
For each of my Value columns, I want to make two calculated columns.
The first will show the difference between the value amounts comparing the previous year for each value type, ie comparing 2023 and 2022 the budget increased by 200 (ie 1200 for 2023 Budget minus 1000 for 2022 Budget) and the actual value increased by 50 (ie 950 for 2023 Actual minus 900 for 2022 Actual).
The second will show the difference between the Budget and Actual amounts for each year, ie in 2022 the desired results would be -100 (ie the 2022 actual value of 900 was less than the budget value of 1000) in the row 2022 Actual
I believe I need to be using the OVER and PREVIOUS functions in my calculated columns, but the statement I have tried does not work as I hoped:
Sum(Value])-Sum([Value]) OVER Intersect ([Type],Previous([Year]))
At the moment my dataset is fairly small so I could feasibly set these up in the raw data using simple Excel functions, but would like to have this defined in Spotfire calculated columns so it doesn’t need maintenance as the data set grows.
After a little bit more reading and some trial and error, I got this working. Posting my solution here so if anyone finds this in the future.
For the first case, I used:
[Value] – First([Value]) OVER (Intersect(Previous([Year]),[Type]))
The takes the value for each row, and deducts the first value (in my case there is only one other row per year) for a row with the previous year and same type.
And for the second case:
[Value] – First([Value]) OVER (Intersect(Next([Type]),[Year]))
This takes the value, and deducts the first value (as above, only one other row) from the next type (ie Budget is after Actual alphabetically, could use Previous if the other way around) for the same year.