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***********************************



      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***********************************



      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****************************



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



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


      Drop Table MinMax1;


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



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



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


      Drop Table MinMax2;


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




      //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********************************




      //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;