topic Re: How to create a calender in QlikView

How to create a calender

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to create a calender

yadhunandhanan — Thu, 11 Aug 2016 09:16:51 GMT

Use this link.

Creating A Master Calendar

Re: How to create a calender

Mark_Little — Thu, 11 Aug 2016 09:17:29 GMT

Hi,

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

Mark

Re: How to create a calender

vcanale — Thu, 11 Aug 2016 09:30:25 GMT

Hi,

The Master Calendar‌ or Calendars‌

hope this can help!

Re: How to create a calender

florentina_doga — Thu, 11 Aug 2016 10:23:12 GMT

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;

Re: How to create a calender

Thu, 11 Aug 2016 11:12:50 GMT

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

Re: How to create a calender

yadhunandhanan — Thu, 11 Aug 2016 11:53:24 GMT

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;