Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Year Range in Master Calendar

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;

0 Replies