Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Line chart Multiple expressions and One line

Hello,

Is it possible to have in a line chart multiple expressions , one dimension in one line.

Expressions are ( sales N-3 ,sales N-2, sales N-1, budget N, sales N), dimension Year

please see the screenshot to better understand my needs

Thank You.

qlikk1.PNG

28 Replies
Sergey_Shuklin
Specialist
Specialist

Can you please provide a screenshot with blue text boxes with yours description of dimensions and expressions? (they are under the chart)

Anonymous
Not applicable
Author

here is the screeshot, now its okey  the budget is display but still have the error msg under the chart,don't know why

qlik22.png

thank you so much

Sergey_Shuklin
Specialist
Specialist

It's ok, some formulas gets this error but still works.

You can go to the Dimensions tab and set a Lable for ValueList() dimension, like "year" (as in my example was).

And I'm glad that you've got the result 😃 now you can do some cosmetics and add some years by changing this parameter:

{">$(=max(ANNEE)-3) <=$(=max(ANNEE)-1)"}

Anonymous
Not applicable
Author

Thank you so much for your help.

All your answers were very richful.

Thank you.

Anonymous
Not applicable
Author

Hi,

I have just one more question if you allow, is it possible to display in the chart the pourcentage highlighted in the screenhot , it represents the evolution from a year to another ( positive or negative).

Thank you so much for your help.

qlikk1.PNG

Sergey_Shuklin
Specialist
Specialist

Hello!

You can do this with additional variable (I called it vVL_evo):

vl_pic2.png

So, all you have to do is to add a previous year as a denominator.

In your case it should be like:

'sum({<ANNEE={'&ANNEE&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&ANNEE&'}>}CA_NET_EN_LITRE)'

/

'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'

-1

for each generated expression.

You can move this expression to the right axis, and then split axis for better vizualization:

vl_pic3.png

For text color you can use this expression:

if(pick(Match(ValueList($(vVL)),$(vVL)),$(vVL_evo))>0, Green(), Red())

If you want to hide dots you may use a transparent color function aRGB(0,0,0,0)

Anonymous
Not applicable
Author

hello , thank you so much for your reply,

here is what i did :

1) i create variable with called vVL_evo with this content :

//prevent error when min year is selected

if(len(Concat(DISTINCT{<ANNEE={">$(=max(ANNEE)-3) <=$(=max(ANNEE)-1)"}>}ANNEE))=0,'sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_DH)/sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_LITRE)'/'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'-1 ,

//create a string with expressions

Concat(DISTINCT{<ANNEE={">$(=max(ANNEE)-3) <=$(=max(ANNEE)-1)"}>}'sum({<ANNEE={'&ANNEE&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&ANNEE&'}>}CA_NET_EN_LITRE)'/'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'-1,',')

)

&','

&'sum({<ANNEE={"$(=max(ANNEE))"}>}BUDGET_CA_NET_DH)/sum({<ANNEE={"$(=max(ANNEE))"}>}BUDGET_CA_NET_LITRE)'/'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'-1 //expression for budget calculation

&','

&'sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_DH)/sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_LITRE)'/ 'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'-1//expression for max year

2) i added the expression in the chart line but i get a memory error and the variable vVL_evo ,didn't even appear in the text box ( please see the screenshot)

is there any error in the variable code??

thank you so much in advance

qlik123.png

Anonymous
Not applicable
Author

is the expression of vVL_evo too much big, and should be optimized because i checked the free memory, the server still have enough free memory , please see the screenchot:

qlik_mem.png

Sergey_Shuklin
Specialist
Specialist

Hello!

There is a typo in condition. Look:

if(

                len(Concat(DISTINCT{<ANNEE={">$(=max(ANNEE)-3) <=$(=max(ANNEE)-1)"}>}ANNEE))=0,

  'sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_DH)/sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_LITRE)

'/'sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE)'-1


You should remove single quotes and enclose divided conditions in brakets:

if(

                len(Concat(DISTINCT{<ANNEE={">$(=max(ANNEE)-3) <=$(=max(ANNEE)-1)"}>}ANNEE))=0,

'(sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_DH)/sum({<ANNEE={"$(=max(ANNEE))"}>}CA_NET_EN_LITRE))/(sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_DH)/sum({<ANNEE={'&(ANNEE-1)&'}>}CA_NET_EN_LITRE))'-1


Should work.

Anonymous
Not applicable
Author

thank you so muh for your help it works now.

However i have one point that i don't need because , logically i should have just three values of the evolution since i have 4 values : sales N-2 , sales N-1, budget N and sales N. the value highlighted in red should not appear. please see the sreenshot .

on the other hand i need in other chart to add more values N-3, N-4 and so on, please can You explain to me the expression of vVL_exp so that i would be able to add more years , thank you so much an im so sorry for distrubiing You .

qlik_evo.png