Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor II

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

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?

Mario

Labels (1)
• ### Variables

4 Replies
Partner - Creator

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.

Contributor II
Author

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?

Partner - Creator

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.

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:

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;

Contributor III

not sure if this could help!

Temp1:

load Spending as Spending23 resident Table where year =2023;

LET vSpend23= peek('Spending23 ');

Tags
Community Browser