Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
meischl
Contributor II
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?

 

Many thanks in advance!

 

Mario

Labels (1)
4 Replies
AronC
Partner - Creator II
Partner - Creator II

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
Contributor II
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?

AronC
Partner - Creator II
Partner - Creator II

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;

Cascader
Creator
Creator

 

not sure if this could help!

 

Temp1:

load Spending as Spending23 resident Table where year =2023;

LET vSpend23= peek('Spending23 ');