From the first table in the attached file, I would like to compare (calculate changes in values in %) first year vs second year and second year vs thrid year. The former compares sum of 2012 - 2015 (2016 should not include as 2016 for the second year is always zero) and the latter compares 2012 - 2014. Is this technically possible ?
Do you use the native time dimension or custom year list? If you use the native time dimension this is simpler but one solution for custom year list also came into my mind. I tried to avoid SELECT-statements and hard coded formulas and wanted to keep in mind that there might be more than three year categories.
Option A) Anaplan timeline
1) Create a module for year categories to define the first and last year for that category. Also define previous year categories.
2) Input contract values to years
3) Add a second line item and a formula to lookup the previous year category value
4) Create a formula using TIMESUM for comparison (1st year would result in -100%, it is ignored)
B) Custom year-list
1) Define previous year category for each year category
2) Map custom years to year categories. Here it is done manually but you could probably have a formula for this.
3) Input contract values and create a second line item with previous year category lookup in the same way than in option A but with the difference that the previous value will be looked up only for years mapped in the step 2.
4) Create the comparison. Again the 1st year must be ignored.