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;
