Hi 🙂
Objective
I have a line chart displaying effect scores pr. year for various departments. I would like each year to be colored (red, yellow or green), based on whether the score of one year is better or worse the the previous year.
My failed attempt
I have written the code below in the color expression box:
if(
(Sum({$<[year]={$(=Max([year]))}>}effect)) > (Sum({$<[year]={$(=Max([year])-1)}>}effect)), green(),
if(
(Sum({$<[year]={$(=Max([year]))}>}effect)) < (Sum({$<[year]={$(=Max([year])-1)}>}effect)), red(),
yellow()
)
)
Using the if structure above, the color should be green if the score is higher than the previoud year, the color should be red if the score is lower than the previous year and yellow if the score is the same.
Problem
The returned line charts for different departments are as shown in the pictures attached to the question.
The line charts do not seem to behave according to the if() structure. Sometimes the effect goes from lower to higher and is colroed red other times it is colored green.
Also, years that are not the most recent year selected or the previous year connected to it just get a yellow color. This, I guess, is do to the MAX statement in the code. But I'm not experienced enough with the syntax to figure out how else to write it :0)
Question
How should i write the code to accomplish my objective?
