0 Replies Latest reply: Oct 7, 2014 6:23 PM by Steve Dillon RSS

    Year Range in Master Calendar

    Steve Dillon

      I have created PubFinancialYear value through my master calendar. This results in transactions falling under the year ranges of 2011-2012, 2012-2013, 2013-2014, etc. I am finding though that my numbers seem to be off when counting transactions based on my PubFinancialYear value. Perhaps I have done something wrong in my calendar settings that is pulling in extra transactions into a year range. Please take a look and let me know if something is going wrong here. MasterCalendarPub is where I declare the PubFinancialYear value. My pub financial year starts in July 1 and ends June 30. Here are my master calendar settings:

       

      //********MinMax Table1***********************************

      MinMax1:

      LOAD

      Min("PUBLISH_DATE") as MinPubDate,

      Max("PUBLISH_DATE") as MaxPubDate

       

      Resident Transactions;

       

      Let vMinPubDate = Num(Peek('MinPubDate', 0, 'MinMax'));

      Let vMaxPubDate = Num(Peek('MaxPubDate', 0, 'MinMax'));

      Let vToday = Today();

       

      //********MinMax Table2***********************************

      MinMax2:

      LOAD

      Min("TRANDATE") as MinTranDate,

      Max("TRANDATE") as MaxTranDate

      Resident Transactions;

       

      Let vMinTranDate = Num(Peek('MinTranDate', 0, 'MinMax'));

      Let vMaxTranDate = Num(Peek('MaxTranDate', 0, 'MinMax'));

       

      //*********Temporary Calendar1****************************

      TempCal1:

      LOAD

      date($(vMinPubDate) + rowno() - 1) AS TempPubDate

        

      AutoGenerate

      $(vMaxPubDate) - $(vMinPubDate) + 1;

       

      Drop Table MinMax1;

       

      //*********Temporary Calendar2****************************

      TempCal2:

      LOAD

      date($(vMinTranDate) + rowno() - 1) AS TempTranDate    

       

      AutoGenerate

      $(vMaxTranDate) - $(vMinTranDate) + 1;

       

      Drop Table MinMax2;

       

      //*********Master Calendar Pub********************************

       

      MasterCalendarPub:

      LOAD

      //Set dates using publish date

      TempPubDate as PressDate,

      Week(TempPubDate) as PubWeek,

      Year(TempPubDate) as PubYear,

      Month(TempPubDate)as PubMonth,

      Day(TempPubDate) as PubDay,

      yearname(TempPubDate,0,7)as PubFinancialYear,

      Weekday(TempPubDate) as PubWeekDay,

      'Q' & Ceil(month(TempPubDate) / 3) as PubQuarter,

      Date(monthstart(TempPubDate), 'MMM-YYYY') as PubMonthYear,

      Week(TempPubDate) &'-'&Year(TempPubDate) as PubWeekYear,

      InYearToDate(TempPubDate, $(vToday), 0) * -1 as CurPubYTDFlag,

      InYearToDate(TempPubDate, $(vToday), -1) * -1 as LastPubYTDFlag

       

      Resident TempCal1

      Order by TempPubDate ASC;

       

      Drop Table TempCal1;

       

      //*********Master Calendar Tran********************************

       

      MasterCalendarTran:

      LOAD

      //Set dates using transaction date

      TempTranDate as TransDate,

      Week(TempTranDate) as TranWeek,

      Year(TempTranDate) as TranYear,

      Month(TempTranDate)as TranMonth,

      Day(TempTranDate) as TranDay,

      yearname(TempTranDate,0,7)as TranFinancialYear,

      Weekday(TempTranDate) as TranWeekDay,

      'Q' & Ceil(month(TempTranDate) / 3) as TranQuarter,

      Date(monthstart(TempTranDate), 'MMM-YYYY') as TranMonthYear,

      Week(TempTranDate) &'-'&Year(TempTranDate) as TranWeekYear,

      InYearToDate(TempTranDate, $(vToday), 0) * -1 as CurTranYTDFlag,

      InYearToDate(TempTranDate, $(vToday), -1) * -1 as LastTranYTDFlag

       

       

      Resident TempCal2

      Order by TempTranDate ASC;

       

      Drop Table TempCal2;