topic Re: Line chart Multiple expressions and One line in QlikView

Line chart Multiple expressions and One line

Anonymous — Thu, 02 Nov 2017 11:21:06 GMT

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

Sergey_Shuklin — Thu, 02 Nov 2017 11:33:26 GMT

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

Anonymous — Thu, 02 Nov 2017 11:43:39 GMT

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

Sergey_Shuklin — Thu, 02 Nov 2017 12:15:08 GMT

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:

load * inline

[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

Anonymous — Thu, 02 Nov 2017 12:30:35 GMT

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.

thank you so much for all your answers.

Re: Line chart Multiple expressions and One line

Sergey_Shuklin — Thu, 02 Nov 2017 12:55:43 GMT

Glad to help 😃

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

Sergey_Shuklin — Thu, 02 Nov 2017 13:15:57 GMT

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

Re: Line chart Multiple expressions and One line

Anonymous — Thu, 02 Nov 2017 15:58:13 GMT

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

Sergey_Shuklin — Fri, 03 Nov 2017 06:35:49 GMT

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

Anonymous — Fri, 03 Nov 2017 09:55:12 GMT

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)&','&chr(39) //additional commas and quotes

&'budget-'&Right(max(ANNEE),2)&chr(39) //add a budget point

&','&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

Please see the screenshot:

Re: Line chart Multiple expressions and One line

Sergey_Shuklin — Fri, 03 Nov 2017 10:23:49 GMT

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

Anonymous — Fri, 03 Nov 2017 10:55:56 GMT

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

Sergey_Shuklin — Fri, 03 Nov 2017 11:07:21 GMT

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

Anonymous — Fri, 03 Nov 2017 11:35:47 GMT

Thank you so much for your help.

All your answers were very richful.

Thank you.

Re: Line chart Multiple expressions and One line

Anonymous — Fri, 03 Nov 2017 14:25:54 GMT

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

Sergey_Shuklin — Sat, 04 Nov 2017 18:10:46 GMT

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

Anonymous — Tue, 07 Nov 2017 16:23:48 GMT

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

Re: Line chart Multiple expressions and One line

Anonymous — Tue, 07 Nov 2017 16:37:32 GMT

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

Sergey_Shuklin — Wed, 08 Nov 2017 04:44:50 GMT

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

Anonymous — Wed, 08 Nov 2017 12:00:39 GMT

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 .