2 Replies Latest reply: Jan 26, 2017 9:13 AM by Vineeth Pujari RSS

    Sort Date into Fiscal Year

    Jia Sheng Loh

      HI all,

      I wanna sort my transaction date into fiscal, which is 1st April - 31th March.

       

       

      I have this as my master calendar,

       

      MinMax:
      LOAD
      Max([A Awardered Date]) as MaxDate,
      Min([A Awardered Date]) as MinDate
      RESIDENT Fact;

       

      LET varMinDate = num(Peek('MinDate',0,'MinMax'));
      LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
      LET vToday = $(vMaxDate);


      Datefield:
      LOAD date($(varMinDate)+IterNo()-1) AS Datefield
      AUTOGENERATE (1)
      WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

       

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

       

      Calendar:
      Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
           Dual(Month, fMonth)              as FMonth,            // Dual financial month
                *;
      Load Year + If(Month>=$(vFM), 1, 0)  as fYear,         // Numeric financial year,
               Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric financial month
                *;
      Load Year(Datefield)                  as Year,           // Your standard master calendar
               Month(Datefield)             as Month,

      Datefield
      Resident Datefield;
      DROP Table Datefield;

       

       

      What happen is that my record are duplicated and different fiscal year.

      Fiancial Year.png

      As shown comparing with FYear and without. I match it with different colour for easier comparison. What would have cause this and how to I fix it?

       

      Regards,

      Frederic

        • Re: Sort Date into Fiscal Year
          Vineeth Pujari

          Create a FiscalYearMonth Field and sort by Expression FiscalYearMonth

          ,

           

          how is your calendar field linked to the your Fact table?? i don;t see the date field that should be linked to your Fact, which is causing  a cartesian join

           

           

          ////////////////////HOW IT SHOULD BE////////////////////////

          MinMax:
          LOAD
          Max([A Awardered Date]) as MaxDate,
          Min([A Awardered Date]) as MinDate
          RESIDENT Fact;

           

          LET varMinDate = num(Peek('MinDate',0,'MinMax'));
          LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
          LET vToday = $(vMaxDate);


          Datefield:
          LOAD date($(varMinDate)+IterNo()-1) AS Datefield
          AUTOGENERATE (1)
          WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

           

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

           

          Calendar:
          Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
               Dual(Month, fMonth)              as FMonth,            // Dual financial month
                    *;
          Load Year + If(Month>=$(vFM), 1, 0)  as fYear,         // Numeric financial year,
                   Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric financial month
                    *;
          Load

                    Datefield as [A Awardered Date],

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

          Datefield
          Resident Datefield;
          DROP Table Datefield;

           

           

          ////////////////////end of script//////////////////

          • Re: Sort Date into Fiscal Year
            Vineeth Pujari

            jiasheng if your query is resolved please close the thread!

            Qlik Community Tip: Marking Replies as Correct or Helpful