7 Replies Latest reply: Apr 14, 2015 6:45 AM by Julien CHOLLET Branched to a new discussion. RSS

    Fiscal year - Order Months in line chart

    Julien CHOLLET

      Hi everyone,

       

      I’m beginning with Qlik Sense and I would like to create a line chart to analyze the changes in cash over a year.

       

      I have tried to create this simple chart using measuring sum (CASH) and size MONTH field.

       

      The problem is that the financial year of my company does not coincide with the calendar year.

       

      In the created graph, months are in chronological order ( January, February ... ), or I would like it to be in the following order :

      July, August , September, October , November, December , January, February , March, April , May, June

       

      Do you know how I can sort the month in that order ?

       

      I tried to change SET FirstMonthOfYear = 1 in to SET FirstMonthOfYear = 7 but the result is wrong :

       

      When I load my data I get this order :

      July, August , September, October , November, December , June, January, February , March, April , May

       

      Do you know why June is loaded after December ?

       

      Thank you for your help

       

      Sincerely,

       

        • Re: Fiscal year - Order Months in line chart
          Sridhar Reddy

          Hi Julien,

           

          Use the below expression in the back end script.

           

          If(Num(Month(CalDate))>6,Num(Month(CalDate))-6,Num(Month(CalDate))+9)

          as [Fiscal Month Number]

           

          add the Fiscal Month Number in sort tab and select option as Ascending order.

           

          Regards,

          Sridhar

          • Re: Fiscal year - Order Months in line chart

            You can always order a text loockup, like MONTH in your case, by disabling the automatic sorting in the sorting options, and enabling the option ORDER BY EXPRESSION. Now you can create your custom expression that associates a sequential ID/number for every MONTH.

             

            In your case the expression (to be ordered increasingly) would be this:

             

            if(MONTH='July',1,

            if(MONTH='August',2,

            if(MONTH='September',3,

            if(MONTH='October',4,

            if(MONTH='November',5,

            if(MONTH='December',6,

            if(MONTH='January',7,

            if(MONTH='February',8,

            if(MONTH='March',9,

            if(MONTH='April',10,

            if(MONTH='May',11,

            if(MONTH='June',12

            ))))))))))))

             

             

            I hope that wolud be helpful. Mark as correct if it is.

             

            Carlo.

            • Re: Fiscal year - Order Months in line chart

              Clearly you can use the same expression in load script, defining the final result with the alias you prefer.

               

              Like this:

               

              LOAD

                   MONTH,

                   if(MONTH='July',1,

                   if(MONTH='August',2,

                   if(MONTH='September',3,

                   if(MONTH='October',4,

                   if(MONTH='November',5,

                   if(MONTH='December',6,

                   if(MONTH='January',7,

                   if(MONTH='February',8,

                   if(MONTH='March',9,

                   if(MONTH='April',10,

                   if(MONTH='May',11,

                   if(MONTH='June',12

                   )))))))))))) as FISCAL_MONTH_ID

               

              after  that, you can always order using this new field.

                • Re: Fiscal year - Order Months in line chart
                  Julien CHOLLET

                  Thank you for your help.

                   

                  I'm sorry but i don't know how to insert this script in my script.

                   

                  Here is my script. Do you mind helping me ?

                   

                   

                  SET ThousandSep=' ';
                  SET DecimalSep=',';
                  SET MoneyThousandSep=' ';
                  SET MoneyDecimalSep=',';
                  SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
                  SET TimeFormat='hh:mm:ss';
                  SET DateFormat='DD/MM/YYYY';
                  SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
                  SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
                  SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
                  SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';
                  SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';
                  SET FirstWeekDay=0;
                  SET BrokenWeeks=1;
                  SET ReferenceDay=0;
                  SET FirstMonthOfYear=1;
                  SET CollationLocale='fr-FR';

                   

                  SET DebutTreso1 = 50700000;
                  SET FinTreso1 = 50879999;
                  SET DebutTreso2 = 50900000;
                  SET FinTreso2 = 51779999;
                  SET DebutTreso3 = 51900000;
                  SET FinTreso3 = 51979999;
                  SET DebutTreso4 = 51990000;
                  SET FinTreso4 = 53999999;


                  //Chargement des FEC et concaténation des fichiers
                  [FEC ORIGINAL]:
                  LOAD *
                  FROM [lib://TDB/FEC N.txt]
                  (txt, utf8, embedded labels, delimiter is '\t', msq);
                  LOAD *
                  FROM [lib://TDB/FEC N-1.txt]
                  (txt, utf8, embedded labels, delimiter is '\t', msq);
                  LOAD *
                  FROM [lib://TDB/FEC N-2.txt]
                  (txt, utf8, embedded labels, delimiter is '\t', msq);

                   

                  //Sauvegarde du fichier concatené en qvd
                  Store [FEC ORIGINAL] into [lib://TDB/FEC ORIGINAL.qvd] (qvd);

                   

                  //Retraitement du FEC
                  [FEC RETRAITE]:
                  LOAD
                      JournalCode as [CODE JOURNAL],
                      JournalLib as [LIBELLE JOURNAL],
                      EcritureNum as [NUMERO ECRITURE],
                      CompteNum as [NUMERO COMPTE],
                      CompteLib as [LIBELLE COMPTE],
                      CompAuxNum as [NUMERO COMPTE AUXILIAIRE],
                      CompAuxLib as [LIBELLE COMPTE AUXILIAIRE],
                      PieceRef as [REFERENCE PIECE],
                      PieceDate as [DATE PIECE],
                      EcritureLib as [LIBELLE ECRITURE],

                      If (Sens='C',Montant * -1,Montant) AS SOLDE,
                      Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],

                    

                  If(CompteNum >=$(DebutTreso1) and CompteNum <=$(FinTreso1) or CompteNum >=$(DebutTreso2) and CompteNum <=$(FinTreso2) or CompteNum >=$(DebutTreso3) and CompteNum <=$(FinTreso3) or CompteNum >=$(DebutTreso4) and CompteNum <=$(FinTreso4),1,0) as FLAGTRESORERIE

                   

                  FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

                   

                  CALENDRIER:
                  LOAD
                      Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],
                      Date(Date#([ValidDate],'YYYYMMDD'),'DD/MM/YYYY') as [EXERCICE],
                      Date(Date#([EcritureDate],'YYYYMMDD'),'MM/YYYY') as [MOIS ANNEE],
                      Month(Date#([EcritureDate],'YYYYMMDD')) as MOIS,
                  Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE


                  FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

                   

                  Sorry if this script is not very professionnel...and sorry for my English...i'm french

                   

                  Thanks

                    • Re: Fiscal year - Order Months in line chart
                      Sridhar Reddy

                      Julien,

                       

                       

                      Insert the expression in the CALENDRIER

                       

                       

                       

                      CALENDRIER:
                      LOAD
                          Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],
                          Date(Date#([ValidDate],'YYYYMMDD'),'DD/MM/YYYY') as [EXERCICE],
                          Date(Date#([EcritureDate],'YYYYMMDD'),'MM/YYYY') as [MOIS ANNEE],
                          Month(Date#([EcritureDate],'YYYYMMDD')) as MOIS,

                      If(Num(Month([EcritureDate]))>6,Num(Month([EcritureDate]))-6,Num(Month([EcritureDate]))+9)

                      as [Fiscal Month Number],

                      Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE


                      FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);

                       

                      After adding the expression in CALENDRIER table reload the application.

                       

                      Click on Edit and select you chart.

                      Go to Sort tab and add =[Fiscal Month Number] expression. select Ascending option.

                       

                       

                      Regards,

                      Sridhar

                      • Re: Fiscal year - Order Months in line chart

                        I try to edit your calendier script using the powerful PRECEDING LOAD statement, wich perform concatenated load statements in a single "table declaration". For more datails you can check this document: Preceding Load

                         

                        I edited the script directly from this blog, I hope there are no syntax errors:

                         

                        CALENDRIER:

                        LOAD

                        *,

                        if(Num(MOIS)=7,1,

                        if(Num(MOIS)=8,2,

                        if(Num(MOIS)=9,3,

                        if(Num(MOIS)=10,4,

                        if(Num(MOIS)=11,5,

                        if(Num(MOIS)=12,6,

                        if(Num(MOIS)=1,7,

                        if(Num(MOIS)=2,8,

                        if(Num(MOIS)=3,9,

                        if(Num(MOIS)=4,10,

                        if(Num(MOIS)=5,11,

                        if(Num(MOIS)=6,12

                        )))))))))))) as FISCAL_MOIS_ID;

                        LOAD
                            Date(Date#([EcritureDate],'YYYYMMDD'),'DD/MM/YYYY') as [DATE ECRITURE],
                            Date(Date#([ValidDate],'YYYYMMDD'),'DD/MM/YYYY') as [EXERCICE],
                            Date(Date#([EcritureDate],'YYYYMMDD'),'MM/YYYY') as [MOIS ANNEE],
                            Month(Date#([EcritureDate],'YYYYMMDD')) as MOIS,
                        Year(Date#([EcritureDate],'YYYYMMDD')) as ANNEE

                         

                        FROM [lib://TDB/FEC ORIGINAL.qvd] (qvd);