2 Replies Latest reply: Jan 13, 2012 6:04 AM by Dennis Hoogenboom RSS

    How to start a monthly chart not in January

      Hello, and thanks in advance for your support.

      My company has a tax year from July to June, so I need that any monthly chart will start in July or if the chart is in weeks,  in the week 27.  How to organize this?.

       

      Best Regards

        • Re: How to start a monthly chart not in January
          Henric Cronström

           

          You can do this by defining a master calendar table already in the script. In the following scriptlet you will find one way to define your fiscal year:

           

          Let vStartOfFiscalYear = 7 ; // July (7) is the first month of the fiscal year

           

          Load distinct
          TransactionDate,
          Month(TransactionDate) as Month,
          Year(TransactionDate) as Year,
          Date(MonthStart(TransactionDate),'MM/YYYY') as YearMonth,
          Dual(Month(TransactionDate), Mod((Month(TransactionDate)-$(vStartOfFiscalYear)),12)+1) as TaxMonth,
          Year(AddMonths(TransactionDate,1-$(vStartOfFiscalYear))) & '/' & Year(AddMonths(TransactionDate,13-$(vStartOfFiscalYear))) as TaxYear
          ;
          Load Date(MakeDate(2011)-1+recno()) as TransactionDate
          autogenerate 732;

           

           

           

          You will need to have a similar load statement, but you should probably consider loading everything from the transaction table:

           

          Load distinct
          TransactionDate,

          resident TransactionTable;

           

            • How to start a monthly chart not in January
              Dennis Hoogenboom

              Here is another way:

               

              Add this to your calender script:

               

              JOIN (Master_Calendar)

              Load

              Week,

              If(Week < 27, Week +26 , Week -26)  as WeekCount

              Resident Master_Calendar;

               

              Now you have a field (WeekCount) which start counting at week 27
              If you sort on Expression SUM(WeekCount) it get the result you want.

               

              Good Luck,

               

               

               

              PS If you dont have a Week field add the following line to your script:

               

              week(DateField)                                                                                 AS Week,