Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to create a calender

Hi All,

I have transaction date column in the excel sheet. I want to show the data in week wise,quarter wise and week ending date.Can anyone help me with this.

6 Replies
Highlighted
Partner
Partner

Highlighted
Partner
Partner

Hi,

There are lots of posts on here about calendars, just search for master calendar and would should find lots of help.

Mark

Highlighted
Partner
Partner

Hi,

The Master Calendaror Calendars

hope this can help!

Highlighted
Partner
Partner

try this

//citesc fisierul cu sarbatorile legale

holydays_t:

LOAD * inline [holydays

1/1/2016

2/2/2016

3/3/2016

7/1/2016]    ;

//concatenez toate sarbatorile legale

NoConcatenate

holydays:

load

    concat(distinct CHR(39)&holydays&CHR(39),',') as concat_holydays

resident holydays_t;

drop table holydays_t;

//creez o variabila cu toate sarbatorile legale concatenate

let v_holydays=peek('concat_holydays',0,'holydays');

drop table holydays;

//exit script;

//creez o tabela de timeline incepand cu 1/1/2016 pana la sfarsitul lunii curente

TempCalendar:

LOAD

    date('1/1/2016' + rowno() - 1,'DD-MM-YYYY') AS DATA

AUTOGENERATE 1 While '1/1/2016' + rowno() - 1< monthend(today())-1;

//la tabela de timeline adaus anul, luna si daca ziua respectiva este lucratoare sau nu (aici tinem cont si de sarbatorile legale)

NoConcatenate

pontaje_finale:

load

    *,   

    NetWorkDays(DATA,DATA,$(v_holydays)) as days_work;

load

    *,

    month(DATA) as luna,

    year(DATA) as an,

       'S'&ceil(Month(date(DATA,'MMM-YY') )/6) as Semestru,

    Year(date(DATA,'MMM-YY') )&'-S'&ceil(Month(date(DATA,'MMM-YY') )/6) as An_Semestru,

    'Q'&ceil(Month(date(DATA,'MMM-YY') )/3) as Trimestru,

    dual(Year(date(DATA,'MMM-YY'))&'-Q'&ceil(Month(date(DATA,'MMM-YY'))/3),num(QuarterStart(date(DATA,'MMM-YY')))) as An_Trim,

    

    date(makedate(year(DATA),month(DATA),'01'),'MMM-YYYY')  as AnLuna,

    floor(monthstart(date(makedate(year(DATA),month(DATA),'01'),'MMM-YY')))  as AnLuna_today,

    'W'&week(date(DATA,'MMM-YY')) as Saptamana,

    dual (year(DATA) &'-w'& week(date(DATA,'MMM-YY')) ,    num(weekstart(date(DATA,'MMM-YY')))) as AnSapt

resident TempCalendar;

drop table TempCalendar;

Highlighted
Not applicable

Thanks,

But i am getting an error as below while reloading the script;

Error is as below:

Table not found

Calender:

Load 

TempDate as [Start Date], 

               week(TempDate) as Week_Number, 

               Year(TempDate) as Year, 

               Month(TempDate) as Month, 

               Day(TempDate) as Day, 

                  WeekDay(TempDate) as WeekDay,

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

                     Resident TempCalendar

                     Order By TempDate ASC

Pls help

Highlighted
Partner
Partner

Use below links

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

http://community.qlik.com/docs/DOC-3858

or Use following script

//***********CREATE VARIABLES***********

LET varMinDate = Num(Peek('Date',0,'TableName'));

LET varMaxDate = Num(Peek('Date',-1,'TableName'));

LET varToday = Num(Today());

//***********TEMPORARY CALENDAR***********

TempDateField:

LOAD

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

date($(varMinDate) + RowNo() -1) as TempDate

AutoGenerate

$(varMaxDate) - $(varMinDate) + 1;

//***********MASTER CALENDAR***********

MasterCalendar:

LOAD *,

  TempDate as OrderDate,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  WeekDay(TempDate) as WeekDay,

  'Q' & Ceil(Month(TempDate)/3) as Quarter,

  Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,

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

  InYearToDate(TempDate,$(varToday),0) * -1 as CurYTDFlag,

  InYearToDate(TempDate,$(varToday),-1) * -1 as LastYTDFlag

Resident TempDateField

Order By TempDate Asc;

DROP Table TempDateField;