Master Calendar with MinDate and MaxDate from table

[#1]

Im attempting to create a master calendar, where

• MinDate = the earliest date from the Column 'REPORT_DATE' in the Table 'TICKET' and
• MaxDate = the latest date from the Column 'REPORT_DATE' in the Table 'TICKET'

Each entry in the Column 'REPORT_DATE' is formated like this"DD-MM-YYYY HH:MM"

I have tried the following

Min_Max:
LOAD
          Date(Floor(Min(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MinDate,
          Date(Floor(Max(Timestamp#(REPORT_DATE, 'DD-MM-YYYY hh:mm')),'DD-MM-YYYY')) as MaxDate
Resident TICKET;

and then created the vaiables and afterwards dropping the above table as well as creating a temp table

LET vMinDate = Peek('MinDate', 0, 'Min_Max');
LET vMaxDate = Peek('MaxDate', 0, 'Min_Max');
DROP Table Min_Max;
Calendar_tmp:
LOAD
          RowNo()          + $(vMinDate) - 1 as TempDate
AutoGenerate $(vMaxDate) - $(vMinDate) + 1;

Here after creating the master calendar

MasterCalendar:
LOAD
          Date(TempDate) as Date,
          Year(TempDate) as Year,
          Month(TempDate) as Month,
          Day(TempDate) as Day,
          Week(TempDate) as Week,
          Weekday(TempDate) as Weekday,
          'Q' & Ceil(Month(TempDate)/3) as Quarter,
          Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,
          Year(TempDate) & '-' & Week(TempDate) as YearWeek
RESIDENT Calendar_tmp;
DROP Table Calendar_tmp;

Im still very new to QlikView, so my attempt has been assembled from various sources and what I could find on the forum, so if you can provide comments with explanation to what Im doing wrong and what I should do instead, it would help me a lot. Thank you very much for your time and help

Sources

• http://community.qlik.com/servlet/JiveServlet/previewBody/3102-102-3-4191/DateFields-TechBrief-US_v2...
• http://community.qlik.com/thread/24869
• http://community.qlik.com/message/174375

[kaushiknsolanki]

Hi,

     Have a look at the example given on this post.

     http://community.qlik.com/thread/24869

Regards,

Kaushik Solanki

[koushik_btech20]

Hello Philip,

Please try with the following calendar application  where you can create Calendar Master :

Step 1)---------------------------------------------------------------------------------------------------------------------------------------

Caltemp:

LOAD

num(min(%Date)) as MinnDate,

num(max(%Date)) as MaxxDate,

max(%TranDate,1) As LastTranDate

resident TableName;

let vLastTransactionDate = date(peek('LastTranDate',0,'Caltemp'));

LET vMinnDate = peek('MinnDate',0,'Caltemp');

LET vMaxxDate = peek('MaxxDate',0,'Caltemp');

DROP Table Caltemp;

DateIsland:

LOAD

    date($(vMinnDate) + RowNo()-1) as D,

    Year($(vMinnDate) + RowNo()-1) as Y,

    Month($(vMinnDate) + RowNo()-1) as M,

    date(MonthStart($(vMinnDate) + RowNo() -1),'MMM-YYYY') as MY,

    week($(vMinnDate) + RowNo() -1) & '-' & Year($(vMinnDate)+RowNo()-1) AS WY,

    YearName($(vMinnDate) + rowno() - 1,0,4) As FullFinYear,

    date(yearstart($(vMinnDate) + rowno() - 1,0,4),'YYYY') AS YearFin,

    '01/' & date((monthsstart(1,date($(vMinnDate) + rowno() - 1),0,4)),'MM') & '/' & date(yearstart($(vMinnDate) + rowno() - 1,0,4),'YYYY') As FinMonYear,

    If(month(date($(vMinnDate) + RowNo()-1)) <4,Floor(month(date($(vMinnDate) + RowNo()-1))+9),Floor(month(date($(vMinnDate) + RowNo()-1))-3)) As MO

    AutoGenerate

    vMaxxDate - vMinnDate + 1 ;

Step 2) -----------------------------------------------------------------------------------------------------------------------------------------------

Calendar:

Load

    D as %TranDate,

    D as TranDate,

    D As InvoiceDate,

    date(D,'DD MMM YYYY') As Date,

    M As Month,

    MO As MonthOrder,

    month(date('01/' & If(month(D) <4,Floor(month(D)+9),Floor(month(D)-3)) & '/' & YearFin))  AS FMonth,

    week(D) As Week,

    (Week(D)-13) As FWeek,

    WeekDay(D) As Weekday,

    WY As WeekYear,

    day(D) As Day,

    date(D,'MM/DD') As DateMMDD,

    date(FinMonYear,'MMM-YYYY') As MonthYear,

    mid(FullFinYear,3,3) & right(FullFinYear,2) As SHYear,

    mid((Left(FullFinYear,4)+1),3,3) &'-'& right((right(FullFinYear,4)+1),2) As NXYear,

    mid((Left(FullFinYear,4)-1),3,3) &'-'& right((right(FullFinYear,4)-1),2) As LYYear,

    FullFinYear As FinYear,

    YearFin As Year,

    'Q' & if(month(D)<4,4,floor(month(D)/3.1)) As Quarter,

    dual('Q' & if(month(D)<4,4,floor(month(D)/3.1)) & ' ' & YearFin,QuarterStart(D)) As QuarterYear

Resident DateIsland Order By Y,MO;

Drop table DateIsland;

Perhaps it may full fill your problem.

Thank you.