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

    Line chart Multiple expressions and One line

    SOUKAINA EZZENFARI

      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

        • Re: Line chart Multiple expressions and One line
          Sergey Shuklin

          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
              SOUKAINA EZZENFARI

              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

                  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
                Sergey Shuklin

                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.

                vl_pic.png

                Hope it will help!

                  • Re: Line chart Multiple expressions and One line
                    SOUKAINA EZZENFARI

                    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:

                    qlik21.png

                      • Re: Line chart Multiple expressions and One line
                        Sergey Shuklin

                        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
                            SOUKAINA EZZENFARI

                            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

                              • Re: Line chart Multiple expressions and One line
                                Sergey Shuklin

                                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
                                    SOUKAINA EZZENFARI

                                    Thank you so much for your help.

                                    All your answers were very richful.

                                     

                                    Thank you.

                                    • Re: Line chart Multiple expressions and One line
                                      SOUKAINA EZZENFARI

                                      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

                                        • Re: Line chart Multiple expressions and One line
                                          Sergey Shuklin

                                          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)

                                            • Re: Line chart Multiple expressions and One line
                                              SOUKAINA EZZENFARI

                                              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

                                                • Re: Line chart Multiple expressions and One line
                                                  Sergey Shuklin

                                                  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
                                                      SOUKAINA EZZENFARI

                                                      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

                                                  • Re: Line chart Multiple expressions and One line
                                                    SOUKAINA EZZENFARI

                                                    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

                                      • Re: Line chart Multiple expressions and One line
                                        Sergey Shuklin

                                        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:

                                        vl_day3_pic1.png

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

                                        vl_day3_pic2.png

                                        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
                                            SOUKAINA EZZENFARI

                                            Hi,

                                            thank you so much for all your helpful answers.

                                            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

                                             

                                            percent.png

                                            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