3 Replies Latest reply: May 10, 2013 7:40 AM by Mertin Strydom RSS

    Financial Year, Quarter, Month, Week and Weekday script...

    Mertin Strydom

      Hi all..

       

      Can anyone help me with the following please?:

       

      Question:

      You are only given the transaction date in your transactions table and your client requires you to be able to show them their data by Financial Year, Quarter, Month, Week and Week Day

      Write the script that will enable you to create the new values for each of the measures listed above. The financial year end is 28 February 2011

      Then confirm the Financial Year, Quarter, Month, Week and Weekday for the following date: 15/06/2011

       

      Thanks

      Mertin

        • Re: Financial Year, Quarter, Month, Week and Weekday script...
          Mohit Sharma

          try this

          LET vDateMin = Num(MakeDate(2011,2,28));

            LET vDateMax = Floor(MakeDate(2011,6,15));

            LET vDateToday = Num(Today());

               TempCalendar:

            LOAD

            $(vDateMin) + RowNo() - 1 AS DateNumber,

            Date($(vDateMin) + RowNo() - 1) AS TempDate

            AUTOGENERATE 1

            WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

          Calendar:

            LOAD

          Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,

                  Day(TempDate) AS CalendarDayOfMonth,

            WeekDay(TempDate) AS CalendarDayName,

          Month(TempDate) AS CalendarMonthName,

            'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

          'FY' & Right( Year(TempDate),2) as YearQtrfy,

          YearName(TempDate,0,2)as FiscalYear

          RESIDENT TempCalendar ORDER BY TempDate ASC;

               DROP TABLE TempCalendar;

          join with your date field table

            • Re: Financial Year, Quarter, Month, Week and Weekday script...
              Mertin Strydom

              Hi, logically I would like to do something like the following, except that I need it in the Qlikview syntax, if you

               

               

              could help me with that please?

               

               

              TestDate = 15/06/2011;

              StartMonth = 3;

              EndMonth = 2;

              CalYear = Year(TestDate);

              CalMonth = Month(TestDate);

               

               

              FinYear = CalYear + 1;

              if CalMonth < StartMonth then FinYear = FinYear - 1;

               

               

              FinMonth = CalMonth - EndMonth;

              if CalMonth < StartMonth then FinMonth = FinMonth + 12;

               

               

              if FinMonth in (1,2,3) then Quarter = 1;

              else if FinMonth in(4,5,6) then Quarter = 2;

              else if FinMonth in(7,8,9) then Quarter = 3;

              else Quarter = 4;

               

               

              Days_Between = TestDate - mdy(EndMonth,28,FinYear-1);

              Week = floor(Days_Between / 52.1775);

              WeekDay = WeekDay(TestDate);

            • Re: Financial Year, Quarter, Month, Week and Weekday script...

              hi

               

              You can map the mastercalender and by using the functions like below

                       

              Date(TransDate) as Dates,

              Month(TransDate) as MonthNumber,

              MonthName(TransDate) as MonthName

              QuarterStart(TransDate),

              Week(TransDate),

              WeekName(TransDate)

               

              the data in terms of month , week, date and many other  can be obtained.

               

              Regards

              Revathy