How can I create a complete calendar ?

[suzel404]

Hi all,

I have data sample and I want to create a complete calendar to visualize :

The STORE_1 IS OPEN FROM 12/12/2013 TO 28/12/2013 IN STEP1

12/12/2013

28/12/2013

12/12/2013- 13-14-15-16-17-18-19-20-21-22-23-24-25-26-27 -28/12/2013

How can I create the days 13 to 27/12/2014 ?

Many thanks for yout help.

[Anonymous]

Hi,

See link http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

See below master calendar template:

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

               min(datefield) as minDate,

               max(datefield) as maxDate

Resident MyTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

let vYesterday = Date(varMaxDate);

DROP Table Temp;

TempCalendar:

LOAD

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

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

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

MasterCalendar:

Load

               TempDate AS DATETIME,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

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

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Hope this helps.

Regards

Neetha

[MK_QSL · Accepted solution]

Use below script..

If you want to include 28/12/2013 also for STORE_1 and STEP_ID 1, use

While DATE_STEP + IterNo() - 1 <= NextDateStep

Temp:
LOAD
  STORE_ID,
  STEP_ID,
  DATE_STEP
INLINE [
STORE_ID,DATE_STEP,STEP_ID
STORE_1,12/12/2013,1
STORE_1,28/12/2013,2
STORE_1,01/01/2014,3
STORE_1,15/01/2014,4
STORE_1,22/01/2014,5
STORE_1,05/02/2014,6
STORE_1,03/03/2014,7
STORE_1,15/04/2014,8
STORE_1,22/04/2014,9
STORE_1,26/04/2014,10
STORE_2,10/12/2013,1
STORE_2,10/01/2014,3
STORE_2,17/01/2014,4
STORE_2,11/02/2014,5
STORE_2,13/02/2014,6
STORE_2,28/03/2014,7
STORE_2,01/04/2014,8
STORE_2,05/04/2014,9
STORE_2,15/04/2014,10
];


A:
Load
  STORE_ID,
  STEP_ID,
  DATE_STEP,
  If(STORE_ID = Previous(STORE_ID),Previous(DATE_STEP)) as NextDateStep
Resident Temp
Order By STORE_ID, DATE_STEP DESC, STEP_ID DESC;
Drop Table Temp;  


B:
Load 
  STORE_ID, STEP_ID, 
  Date(DATE_STEP) as DATE_STEP,
  Year(Date(DATE_STEP)) AS CALENDAR_YEAR,
  Month(Date(DATE_STEP)) AS CALENDAR_MONTH,
  Date(DATE_STEP) AS CALENDAR_DATE,
  Day(Date(DATE_STEP)) AS CALENDAR_DAY;
Load 
  STORE_ID,
  STEP_ID,
  DATE_STEP + IterNo() - 1 as DATE_STEP
Resident A
While DATE_STEP + IterNo() - 1 < NextDateStep
Order By STORE_ID, STEP_ID, DATE_STEP;
Drop Table A;

[suzel404]

Thank you Neetha P ,  Manish it is exactly that I want.

Many Thanks

[MarcoWedel]

Hi,

one solution could be also:

tabStoreSteps:
LOAD * INLINE [
STORE_ID,DATE_STEP,STEP_ID
STORE_1,12/12/2013,1
STORE_1,28/12/2013,2
STORE_1,01/01/2014,3
STORE_1,15/01/2014,4
STORE_1,22/01/2014,5
STORE_1,05/02/2014,6
STORE_1,03/03/2014,7
STORE_1,15/04/2014,8
STORE_1,22/04/2014,9
STORE_1,26/04/2014,10
STORE_2,10/12/2013,1
STORE_2,10/01/2014,3
STORE_2,17/01/2014,4
STORE_2,11/02/2014,5
STORE_2,13/02/2014,6
STORE_2,28/03/2014,7
STORE_2,01/04/2014,8
STORE_2,05/04/2014,9
STORE_2,15/04/2014,10
];

LOAD STORE_ID,
    Date(DATE_STEP+IterNo()) as DATE_STEP,
    STEP_ID
While DATE_STEP+IterNo()<=PrevDate;
LOAD STORE_ID,
    DATE_STEP,
    Previous(DATE_STEP) as PrevDate,
    STEP_ID
Resident tabStoreSteps
Where STORE_ID=Previous(STORE_ID)
Order By STORE_ID, DATE_STEP desc;

tabCalendar:
LOAD *,
    DATE_STEP AS CALENDAR_DATE,
    Day(DATE_STEP) as CALENDAR_DAY,
    WeekDay(DATE_STEP) as CALENDAR_WEEKDAY,
    Week(DATE_STEP) as CALENDAR_WEEK,
    WeekName(DATE_STEP) as CALENDAR_WEEKNAME,
    Month(DATE_STEP) as CALENDAR_MONTH,
    MonthName(DATE_STEP) as CALENDAR_MONTHNAME,
    Dual('Q'&Ceil(Month(DATE_STEP)/3),Ceil(Month(DATE_STEP)/3)) as CALENDAR_QUARTER,
    QuarterName(DATE_STEP) as CALENDAR_QUARTERNAME,
    Year(DATE_STEP) as CALENDAR_YEAR,
    WeekYear(DATE_STEP) as CALENDAR_WEEKYEAR;
LOAD Date(MinDate+IterNo()-1) as DATE_STEP
While MinDate+IterNo()-1<=MaxDate;
LOAD Min(DATE_STEP) as MinDate,
    Max(DATE_STEP) as MaxDate
Resident tabStoreSteps;

hope this helps

regards

Marco

[MarcoWedel]

olumn headers changed for improved readability:

hope this helps

regards

Marco

[suzel404]

Hi Marco,

Very interesting solution!!

[#8]

Hello,

I need to do an evolution to my calendar date.

Until the STORE_ID don't have a DATE_STEP for STEP_ID 7, the DATE_STEP increment until Today().

Thank for your help.

[#9]

Hello joshu,

By using Master calander concept you can get those dates.

Here you go....