Master Calendar

[asb02512]

Dear all

I have two date fields. One in Sales_HDR as SALE_DATE and other one is Sales_RTRN_HDR as SALE_RETURN_DATE

i want to create a master calendar using these two date fields. Can anyone please guide me. how to do that ????

Thanks.

Sayeed

[girirajsinh]

Below works for me to create master calendar

---------

make your Start, End based on Sales_HDR and Sales_RTRN_HDR

I just used following

LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -2)));
LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

Calendar:
LOAD
     CALENDAR_DATE AS %KEY_CALENDAR_DATE,
     DATE(CALENDAR_DATE) AS Date,
     YEAR(CALENDAR_DATE) AS Year,
     MonthName(date(CALENDAR_DATE)) as MonthName,
     MONTH(CALENDAR_DATE) as MonthShort,
     DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS Month,
     NUM(MONTH(CALENDAR_DATE)) AS Month_num,
     NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS YYYYMM,

//    DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS YYYYMMDD,
    DAY(CALENDAR_DATE) AS Day,
//    DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS Weekday,
    'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS Quarter,
//    DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
//         NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
//    DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
        YEAR(WEEKSTART(CALENDAR_DATE)) &  NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00') AS WeekC

;
LOAD
     ($(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

[hector_munoz]

Hi Abu Sayeed Baseer,

A possible solution would be joinning both fact tables into one and create a calendar from the date field, as follows:

// Tables with sales data

Sales_HDR:

LOAD * INLINE [

Product SHDR, Shop SHDR, Date SHDR, Sales SHDR

P01, S1, 02/01/2017, 23

P01, S1, 03/01/2017, 23

P02, S1, 04/01/2017, 12

P02, S1, 05/01/2017, 12

P03, S1, 02/01/2017, 14

P01, S2, 02/01/2017, 23

P01, S2, 02/01/2017, 23

P01, S2, 04/01/2017, 23

P02, S2, 02/01/2017, 12

P02, S2, 05/01/2017, 12

P02, S2, 07/01/2017, 12

];

// Tables with returns data

Sales_RTRN_HDR:

LOAD * INLINE [

Product RTRN, Shop RTRN, Date RTRN, Sales RTRN

P01, S1, 04/01/2017, 23

P01, S2, 06/01/2017, 23

P02, S2, 07/01/2017, 12

];

// Table with concatenated data

Sales:

LOAD 'Sales' AS Type,

  [Product SHDR] AS Product,

  [Shop SHDR] AS Shop,

  Num([Date SHDR]) AS [Num Date],

  [Sales SHDR] AS Sales

RESIDENT Sales_HDR;

CONCATENATE (Sales)

LOAD 'Returns' AS Type,

  [Product RTRN] AS Product,

  [Shop RTRN] AS Shop,

  Num([Date RTRN]) AS [Num Date],

  -[Sales RTRN] AS Sales

RESIDENT Sales_RTRN_HDR;

// Calendar table

CALENDAR:

LOAD *,

  Year([Num Date]) AS Year,

  Month([Num Date]) AS Month,

  Day([Num Date]) AS Day;

LOAD DISTINCT [Num Date]

RESIDENT Sales;

I attach the QV app.

Regards,
Héctor

[rwunderlich]

See this tutorial for one approach to handling multiple date fields with a single calendar.  The sample file is available in both Sense and QlikView versions.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[MK9885]

Create a DateID field for both Fact and Master.

It depends on which date you want to be as Key?

If you want

Trim(Date(SALE_DATE,'YYYYMMDD') as DateID

or

Trim(Date(SALE_RETURN_DATE, 'YYYYMMDD') as DateID

In your fact table

And in Master Table

Trim(Date(TempDate,'YYYYMMDD') as DateID,

Date(TempDate,'MM/DD/YYYY') ad [Effective Date]

Note: TempDate is temporary date field you're creating to get all calendar dates.

It can be either and I don't think it should matter which one is Key (experts here might do it differently) but as long as you've Effective Date in place for Master.

The above field will make a key from your Fact to Master.

Try below Master Script

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap: 

    MAPPING LOAD  

    rowno() as Month, 

    'Q' & Ceil (rowno()/3)  as Quarter 

    AUTOGENERATE (12); 

       varMinDate = num(date(mid('2000-01-01',1,10 ),'YYYY-MM-DD'));

//       varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));

       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar: 

    LOAD 

                   $(varMinDate) + Iterno()-1 as Num, 

                   Date($(varMinDate) + IterNo() - 1) as TempDate 

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

// Date Dimension

MasterCalendar: 

LOAD

  *,

  [Quarter Number]-1 as PQ,

  if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

// If(Year<=$(vLast5Year),Year,If(Year>=$(vLast5Year),Year )) as Years,

// If (Year >= $(vLast5Year), Quarter) as Quarter_5Year,

// If (Year >= $(vLast5Year), Month) as Month_5Year,

  AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

  AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

Load 

trim(date(TempDate,'YYYYMMDD')) as [DateID],

  date(TempDate,'MM/DD/YYYY') as [Effective Date],

  if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

    day(TempDate) as Day,

    TempDate as [US Calendar Format],

    date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

    date(TempDate,'WWWW') as [Full Day Name],

    year(TempDate) as Year,

    inyear(TempDate,today(),0) * -1   as [CY],    // Current Year

    inyear(TempDate,today(),-1) * -1 as [First PY],

    inyear(TempDate,today(),-2) * -1 as [Second PY],

    inyeartodate(TempDate,today(),0) * -1   as [CYTD],

    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,

if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

    inquarter(TempDate,today(),0) * -1 as [CQ],  // Current Quarter

    //    if(InQuarter(TempDate, today(),-1), 1, 0) as [PQ1],  // Previous Quarter

    inquarter(TempDate,today(),-4) * -1 as [First PQ],

    inquarter(TempDate,today(),-8) * -1 as [Second PQ], 

    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

    date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,                  

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

  date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

  date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

    week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

    weekDay(TempDate) as [Week Day],

    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12],

    If( TempDate > monthstart(addmonths(today(),-2)) and TempDate <= today(),1) as [Rolling 3]

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

Drop Table  QuarterNAME;

Also check the Date format you're using Vs this script Date format which might be different.

Thanks,

[ahaahaaha]

Hi Sayeed,

I recommend additionally look

Generating Missing Data In QlikView

Andrey