Comparing Difference in Columns of A Matrix (Dynamic Column Values)

[Anonymous]

Hi guys

I've got a matrix that uses 2 columns from 2 tables - column 'Year' from table 'Programme' and column 'Type' from 'PersonTypes'. In the rows of the matrix I have Type and in the columns I have Year, with a count of the Type in the values. It looks along the lines of this:

Type       2018   2019   2020

Type1        3        2        5

Type2        1        2        5

Type3        0        2        6

Type4        8        1        5

What I want to do is add a calculated column on the end that will calculate the difference in percentage between a selected column and the previous years column (where the current year is selected by default). So possibly something like this (if column 2020 was selected):

Type       2018   2019   2020    Difference

Type1        3        2        5            150

Type2        1        2        5            150

Type3        0        2        6            200

Type4        8        1        5            400

I have seen posts on other threads for queries similar to this but they seem to be hard coded for the the columns needed for comparison. The number of columns I have is dynamic and will increase as data is added for new years into the Programme table (2021,2022 etc), and I would preferably want the columns for comparison to default to any new years automatically for comparison. Is this possible and how exactly can I do it?

Any help or advice welcome please. Thank you

[lbendlin]

this basically boils down to the "second best"  problem.  You can use variables or TREATAS to resolve that

With variables it would be something like this

Var a = currentyear

Var b = currentyearvalue

var c = calculate(value, lastnonblank(year), year< a )

Return (b-c)/c*100

[stevedep]

Hi,,

Can you explain the difference column with a few examples?