4 Replies Latest reply: Jul 26, 2011 11:37 AM by Rodrigo Almeida RSS

    Qlikview Bug? I really can't sort Fiscal Month Calendar! Help me!

      Hi guys,

       

      I'm with a problem to sort a list box with month, where this month is a month calendar.

      The fiscal year here starts on Abril to March.

       

      When I using the list box and sorting by expression, could be FiscalPeriod or sum(all FiscalPeriod), the wlk works fine.

      I mean:

      Fiscal Month, Fiscal Period

      Apr   1

      May  2

      Jun   3

      ...

      Mar  12

       

      But, the active sheet needs to select the last 3 months.

      I did it by a macro using a trigger on active sheet.

       

      I don't know why!! Qlikview works fine for few minutes and after sort automatically from Jan to Dec.

      I've search a lot of thing here in forum, but anyone works fine to me.

       

      Please, I need your help.

      I'm sending a test qlik for your analysis.

       

      Thank you,

      Rodrigo.

        • Qlikview Bug? I really can't sort Fiscal Month Calendar! Help me!
          Piet Hein van der Stigchel

          'all' is not a command, 'Total' is, so use sum(TOTAL FiscalPeriod) ?

          • Re: Qlikview Bug? I really can't sort Fiscal Month Calendar! Help me!
            Gethyn Owen

            Hi Rodrigo,

             

            Here is the code for how I have created a master calender for financial years.

             

             

            MonthMap:
            Mapping LOAD * INLINE [
                PERIOD_NUM, Month
                1, April
                2, May
                3, June
                4, July
                5, August
                6, September
                7, October
                8, November
                9, December
                10, January
                11, February
                12, March
                13, Adjusting Period
            ];
            
            
            PeriodsTemp:
            LOAD
                      "PERIOD_NAME",
                      right("PERIOD_NAME",7)&'-'&left("PERIOD_NAME",3) as Period,
                "PERIOD_SET_NAME",
                "START_DATE",
                "END_DATE",
                "PERIOD_TYPE",
                "PERIOD_YEAR",
                num("PERIOD_YEAR")-1&'/'&right(num("PERIOD_YEAR"),2) as [Financial Year],
                "PERIOD_NUM",
                ApplyMap('MonthMap', PERIOD_NUM) as Month,
                "QUARTER_NUM",
                'Q'&"QUARTER_NUM" as [Quarter Name];
            SQL SELECT *
            FROM APPS."GL_PERIODS_V"
            Where "PERIOD_TYPE" = '21';
            Store PeriodsTemp into PeriodsTemp.qvd;
            Drop Table PeriodsTemp;
            
            
            LET varMinDate = num('01/04/2007');
            LET varMaxDate = num('31/03/2015');
            
            
            Dates:
            LOAD date($(varMinDate)+recno()-1) as Date
            AUTOGENERATE $(varMaxDate)-$(varMinDate)+1;
            
            
            Periods:
                      LOAD 
                                "PERIOD_NAME",
                                Period,
                          "PERIOD_SET_NAME",
                          "START_DATE",
                          "START_DATE" + IterNo() -1 as Date,
                          "END_DATE",
                          "PERIOD_TYPE",
                          "PERIOD_YEAR",
                          "Financial Year",
                          "PERIOD_NUM",
                          Month,
                          "QUARTER_NUM",
                          "Quarter Name"
                      FROM
                      PeriodsTemp.qvd (qvd)
                      While "START_DATE" + IterNo() -1 <= "END_DATE";
                      Store Periods into MasterCalendar.qvd;
            
            
            Drop Table Periods;
            
            

             

            The APPS."GL_PERIODS_V table is from our Oracle database and defines the start and end dates for each financial period and also determines which financial year and quarter that period is in.

             

            The auto generate statement fills in all the missing dates between each start and end date.

             

            You should be able to achieve the same end result by using an Excel spread sheet instead of the APPS."GL_PERIODS_V tabe to define the period start and end dates.

             

            Hope this helps,

             

            Gethyn.