28 Replies Latest reply: Nov 20, 2017 11:47 PM by Sergey Shuklin

# 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.

• ###### Re: Line chart Multiple expressions and One line

Hello!

For such charts you can use ValueList() function for defining different dimensions.

So, when you select Line Chart go to the dimensions tab, click on Add Calcalated Dimension and write the followin:

ValueList('2010', '2011', '2012', '2013b', '2013')

As Expression you should use such syntaxis:

if(ValueList('2010', '2011', '2012', '2013b', '2013')='2010', sum({<year={2010}>}sales),

if(ValueList('2010', '2011', '2012', '2013b', '2013')='2011', sum({<year={2011}>}sales),

and so on

After that you can copy this value, make another one with dots (instead of lines) and change sum() function to some relative calculations.

• ###### Re: Line chart Multiple expressions and One line

thank you so much for answring quickly.

The solution you gave me suppose that the chart is fixe, i mean im going to have always the same years, however the final user want it to be dynamic , if he select year 2016 , the year N-1 going to be 2015 , the year N-2= 2014 and so on. so the result depends on the year selected.  i think i need to use set analysis with year = max(year).

but it doesn't gave me the view i want.

Thank you so much.

• ###### Re: Line chart Multiple expressions and One line

Hm, in that conditions you can do a little trick.

The ValueList() function can be used with dynamic generated list of values.

You should use Concat() function to reach this, but first let take a look at this table:

[year, val

2011,20

2012,15

2013,13

2014,7

2015,18];

It's little different from yours but it's made just for good explanation.

Now, if you use this construction as variable:

=chr(39)&concat({<year={">\$(=max(year)-3)"}>}year,chr(39)&','&chr(39))&chr(39)

Call this variable vVL. It will return the list of year depending on User's selection.

After that we can use not just ValueList(2011, 2012...) but ValueList(\$(vVL))

And expression will be:

pick(match(ValueList(\$(vVL)),\$(vVL)), sum({<condition for year - 3>}sales), sum({<condition for year - 2>}sales), sum({<condition for year - 1>}sales) and so on)

It's little complicate, but more flexible, because you can made different calculations in the front-end.

If you wish I can prepare some example (for now I don't have such opportunity - locating on workplace).

• ###### Re: Line chart Multiple expressions and One line

thank you so much for your help.

i will try that method,  if i fail in getting the result, i will ask you for a qvw example if its possible.

• ###### Re: Line chart Multiple expressions and One line

Found a little mistake. The vVL expression should be like

=chr(39)&concat({<year={">\$(=max(year)-3) <=\$(=max(year))"}>}year,chr(39)&','&chr(39))&chr(39)

to make list of three values (in previous expression returns only one - which was selected)

• ###### Re: Line chart Multiple expressions and One line

Found a way to bring you a little example. Hope it will help!

• ###### Re: Line chart Multiple expressions and One line

Hi,

I tried to display the chart basing on what i understand ( im sorry im afraid of not really understanding the solution you proposed).

what i did is :

for the expressions :  N-3 : Sum({\$<ANNEE={\$(=MAX(ANNEE)-3)}>}CA_NET_EN_DH)

N-2 : Sum({\$<ANNEE={\$(=MAX(ANNEE)-2)}>}CA_NET_EN_DH)

N-1 :Sum({\$<ANNEE={\$(=MAX(ANNEE)-1)}>}CA_NET_EN_DH)

Budget N :Sum({\$<ANNEE={\$(=MAX(ANNEE))}>}CA_NET_EN_DH)

Sales N : Sum({\$<ANNEE={\$(=MAX(ANNEE))}>}BUDGET_CA_NET_EN_DH)

Dimensions:         Year

here is the result i get: its not really what i want

l need to have continous line between the points and for the year N i need to have separate points for sales and budget, juste like this:

• ###### Re: Line chart Multiple expressions and One line

You set four expressions and the chart is returning four dots. There is a little more different way to achieve this.

Please check the picture below and the attachement.

I think you can implement this solution to your project just with renamed fields.

Hope it will help!

• ###### Re: Line chart Multiple expressions and One line

Thank you so much for your help,

i implemented the solution you proposed and it seems working. Im juste facing a little probleme , the budget point is not displaying do. (Error garbage after expression :see screenshot)

the expression i ued is calculated = CA_NET_EN_DH / CA_NET_EN_LITRE

here is the dimension i use (vVL variable):

=chr(39)&

//condition to prevent error when min year is selected

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

//combining year field values to the string separated by comma and single quotes

Concat(DISTINCT{<ANNEE={">\$(=max(ANNEE)-3) <=\$(=max(ANNEE)-1)"}>}ANNEE,chr(39)&','&chr(39))

)

&','&chr(39)&max(ANNEE)&chr(39) //add the last point with max year

and the expression (vvl_exp):

=

//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)',

//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={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_DH)/sum({<ANNEE={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_LITRE)' //expression for budget calculation

&','

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

• ###### Re: Line chart Multiple expressions and One line

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

• ###### Re: Line chart Multiple expressions and One line

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

thank you so much

• ###### Re: Line chart Multiple expressions and One line

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)"}

• ###### Re: Line chart Multiple expressions and One line

Thank you so much for your help.

Thank you.

• ###### Re: Line chart Multiple expressions and One line

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.

• ###### Re: Line chart Multiple expressions and One line

Hello!

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

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:

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)

• ###### Re: Line chart Multiple expressions and One line

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

• ###### Re: Line chart Multiple expressions and One line

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.

• ###### Re: Line chart Multiple expressions and One line

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 .

• ###### Re: Line chart Multiple expressions and One line

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:

• ###### Re: Line chart Multiple expressions and One line

Hello!

It's Ok, I always glad to help =)

For hiding some dots and texts you can use this additional row on background color condition:

And as you can see I've added one more year to the chart. You can do so by changing just one parameter:

Let's call it a "year parameter".

And I think, if you need to use another chart with different number of years, then it should be a new couple of variables, like vVL_chart2, vVL_exp_chart2 and vVL_evo_chart2 with "year parameter" equal 5 or 6 or whatever you needed.

Hope I understood you right with dots issue and please don't be shy for asking!

• ###### Re: Line chart Multiple expressions and One line

Hi,

i have now the result i want ; i added one more year to the gaphe and i calculate the evolution in percentage which is the difference between the dots. but i have two percentage that are not correct  please see the screenshot.

(2,36/2,32)-1 = 1,71    --> OK

(2,4/2,36)-1 = 1,69  --> KO

(2,42/2,4)-1 = 0,83    --> OK

(2,47/2,42)-1 = 2,066    --> KO

here is the code of the vVL_evo :

=

//prevent error when min year is selected

if(len(Concat(DISTINCT{<ANNEE={">\$(=max(ANNEE)-4) <=\$(=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)-4) <=\$(=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={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_DH)/sum({<ANNEE={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_LITRE))-1'//expression for max yea

the sales indicator in my case is '(sum({<ANNEE={"\$(=max(ANNEE))"}>}CA_NET_EN_DH)/sum({<ANNEE={"\$(=max(ANNEE))"}>}CA_NET_EN_LITRE))'

the budget indicator is : '(sum({<ANNEE={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_DH)/sum({<ANNEE={"\$(=max(ANNEE))"}>}BUDGET_CA_NET_LITRE))'

thank you so much

• ###### Re: Line chart Multiple expressions and One line

Hello!

I don't think that there are some mistakes. Check the precision of values, because for 2016 year it will be: 2,36 x 1,019361 = 2,40

• ###### Re: Line chart Multiple expressions and One line

Thank you for your answer, but for the year 2016 the evolution i have Is 1,9361%

however the right value is :

(2,4/2,36)-1=0,01694 and in percentage it gives : 1,69 %

i have the same precision  for all the points why just 2016 and 2017 give me a wrong values.

• ###### Re: Line chart Multiple expressions and One line

Hello!

Can you please add more decimals to the values on the chart and place a screenshot?

• ###### Re: Line chart Multiple expressions and One line

yes of course

here is the screenshot

• ###### Re: Line chart Multiple expressions and One line

Hello!

I've made some calculating things in Excel and here what I've got:

 year dh/litre Excel QV diff 2014 2,3158 - - - 2015 2,3555 1,7143% 1,7164% 0,0021% 2016 2,4011 1,9359% 1,9361% 0,0002% budget 2,4212 0,8371% 0,8376% 0,0005% 2017 2,4716 2,0816% 2,0801% -0,0015%

And I think if you'll add more decimals (about ten) you'll get the "diff" column values about absolute zero.

• ###### Re: Line chart Multiple expressions and One line

thank you so much for your help,

So i can't have the right value with two numbers after the commas.

• ###### Re: Line chart Multiple expressions and One line

Hello!

You can make any changes in vVL_evo expressions output list, even roundind. Should be something like this:

Concat(

DISTINCT{<ANNEE={">\$(=max(ANNEE)-4) <=\$(=max(ANNEE)-1)"}>}

'Round(

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

,0.01)

/

Round(

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

,0.01)

-1'

,',')