I want to create a line chart where I can change the curve with variables

[meischl]

I have created a table with a crosstable load script which looks like this:

Year	Spending
2023	10000
2024	12000
2025	10000
2026	11000
2027	12000

From this data I'm creating line chart where Year is the Line Dimension and Spending the Measure.

What I would like to do now is to use following variables enabling me to update the spending of each calendar year separately:

vSpend23

vSpend24

vSpend25

vSpend26

vSpend27

Does anyone have an idea how this could be accomplished?

Many thanks in advance!

Mario

[AronC]

If you want something quick and dirty and don't mind its hardcoded and not very dynamic.

Sum(Spending) + sum({<Year={'2023'}>}$(vSpend23)) + sum({<Year={'2024'}>}$(vSpend24)) .....
Then you add as many variable input boxes as you need.

[meischl]

AronC,

Thank you very much for your help! Your suggestion helped me a lot. Unfortunately, I couldn't use it 1:1. Somehow your formula always applied the value of one year to all years. A change in the vSpend23 variable raised the curve for all years by this change.

I ended up finding a solution by modifying your formula using the good old "if" approach:

Sum(Spending) + if(Year=’2023’,vSpend23,0) + if(Year=’2024’,vSpend24,0) …

Do you happen to have an idea why it only works like that? Did I do something wrong?

[AronC]

The expression I posted should work fine. With the dataset you provided, there shouldn't be any reason why it doesn't work unless there is a writing-error or you model is more complex and takes other things into account. 
For your information the if-statements should be avoided in frontend as long as you can for performance reasons. In this small case it doesn't matter, but if your model is bigger you should try to avoid if.
If the answer helped you solve your problem, please mark as solution.

Cheers!



Pasting the expression again if I might have made a typo before. 

Sum(Spending) + sum({<Year={'2023'}>}$(vSpend23)) + sum({<Year={'2024'}>}$(vSpend24))+ sum({<Year={'2025'}>}$(vSpend25))

Backend I have the following code:

load *
inline [

Year, Spending
2023, 10000
2024, 12000
2025, 10000
2026, 11000
2027, 12000
]
;

set vSpend23 =0;

set vSpend24 =0;

set vSpend25 =0;

set vSpend26 =0;

set vSpend27 =0;

exit Script;

[Ameg]

not sure if this could help!

Temp1:

load Spending as Spending23 resident Table where year =2023;

LET vSpend23= peek('Spending23 ');