Year Range in Master Calendar

Tue, 07 Oct 2014 22:23:00 GMT

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;

topic Year Range in Master Calendar in QlikView

Year Range in Master Calendar