8 Replies Latest reply: Jan 23, 2017 3:09 AM by Jia Sheng Loh RSS

    Multiple Line Chart

    Jia Sheng Loh

      Hello Experts!

       

      I would like to create this on multi line chart Qlik Sense, how to do it?

       

      (something like this)

      Multi line Example.png

                                                     (This is drawn using MS Paint, cause I do not know how to do it in Qlik Sense)

       

      My Date data is DD/MM/YYYY format, so I have to script it to sort by year? How?

       

      For the x-axis, will it be multiple measures of count([Fruits])?

       

      Thank you!

       

       

        • Re: Multiple Line Chart
          Devarasu R

          Hi,

           

          Like this?

          Sample.jpg

            • Re: Multiple Line Chart
              Jia Sheng Loh

              HI,

               

              My data do not have [Year],

               

              Date, Category, Sales

                30/12/2016, Apple, 322

                30/12/2016, Orange, 567

                30/12/2016, Pear, 754

                30/12/2016, Grape, 897

                30/12/2016, Banana, 843

                1/1/2017, Apple, 675

                1/1/2017, Orange, 333

                1/1/2017, Pear, 222

                1/1/2017, Grape, 621

                1/1/2017, Banana, 978

                11/11/2015, Apple, 322

                11/11/2015, Orange, 533

                11/11/2015, Pear, 632

                11/11/2015, Grape, 112

                11/11/2015, Banana, 543

               

               

              How do I use the year from my [Date] data?

                • Re: Multiple Line Chart
                  Devarasu R

                  Hi,

                   

                  Try this,

                   

                  Temp:

                  LOAD * INLINE [

                    Date, Category, Sales

                    30/12/2016, Apple, 322

                    30/12/2016, Orange, 567

                    30/12/2016, Pear, 754

                    30/12/2016, Grape, 897

                    30/12/2016, Banana, 843

                    1/1/2017, Apple, 675

                    1/1/2017, Orange, 333

                    1/1/2017, Pear, 222

                    1/1/2017, Grape, 621

                    1/1/2017, Banana, 978

                    11/11/2015, Apple, 322

                    11/11/2015, Orange, 533

                    11/11/2015, Pear, 632

                    11/11/2015, Grape, 112

                    11/11/2015, Banana, 543

                  ];

                   

                   

                  Fact:

                  load  *,year(Date) as Year resident Temp;

                   

                   

                  drop table Temp;

                   

                  Note: above method is vai script option and you can do it front itself and (year sorting also you can do it in both end)

                   

                  If you found this helpful please mark it  and close this Issue


                  Thanks,Deva

                    • Re: Multiple Line Chart
                      Petr Kyncl

                      Yes. Function Year convert date format to Year. It will be your new dimension.

                      • Re: Multiple Line Chart
                        Jia Sheng Loh

                        Thanks Devarsu!

                         

                        I actually need to sort it by financial year, which is:

                         

                        1 April 2015 - 31 March 2016

                        1 April 2016 - 31 March 2017

                        ......

                         

                        how would the script be?

                         

                        Sorry for not clarifying earlier.

                          • Re: Multiple Line Chart
                            Devarasu R

                            Hi,

                             

                            you need to have below

                             

                            Let varMinDate = Num(Makedate(2016,1,1));

                            Let varMaxDate = Num(Makedate(Year(today()),Month(today()),Day(today())));

                             

                            Datefield:

                            LOAD date($(varMinDate)+IterNo()-1) AS Datefield

                            AUTOGENERATE (1)

                            WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

                             

                            Set vFM = 4 ;                                                          // First month of fiscal year

                             

                            Calendar:

                            Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

                                 Dual(Month, fMonth)              as FMonth,            // Dual fiscal month

                                      *;

                            Load Year + If(Month>=$(vFM), 1, 0)  as fYear,         // Numeric fiscal year

                                     Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric fiscal month

                                      *;

                            Load Year(Datefield)                  as Year,           // Your standard master calendar

                                     Month(Datefield)             as Month,

                            Datefield

                            Resident Datefield;

                            DROP Table Datefield;

                            • Re: Multiple Line Chart
                              Devarasu R

                              Hi Jia,

                               

                              Refer to the attachment with  Financial calendar method, Thanks

                               

                              Thanks, Deva

                               

                              Please mark the answer as Helpful or close the thread by marking the correct answer.

                              1.png

                                • Re: Multiple Line Chart
                                  Jia Sheng Loh

                                  Hi Deverasu,

                                   

                                  Trying the script, I found that some record with same date have different financial year, for example you attached, 30/12/2016 falls under fiscal year 2016 and 2017. It should be 2016 as it has not cross over to 31/2/2017?