Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;