Year on Year score change

[helen_c]

Hi - I'm looking for advise on creating a yr on yr score change.

I have 5 years of data with scores for various university subjects like this:

2017, Maths, 82

2019 Maths, 80

2020, Maths, 79

2021, Maths, 86

2017, English, 82

2018, English, 90

2019 English, 80

2021, English, 86

I've used the Multi row formula to successfully create a Yr on yr score change however some subjects don't have scores for every year (In the example above Maths doesn't have a score for 2018 and English for 2020.

I would like the yr on yr score to be null (or blank) if the previous year doesn't have a score. Please could somebody advise the best way to achieve this.

Thanks in advance!

[Kenda · Accepted solution]

Hi @helen_c 

Try this expression in your Multi-Row Formula tool:

iif([Row-1:Year]=[Year]-1, [Score]-[Row-1:Score], null())

Don't forget to group by the subject field!

[helen_c]

that worked perfectly - thank you.