topic Re: Create date periode (Tertial) starting at September in QlikView

Create date periode (Tertial) starting at September

jjordaan — Fri, 16 Mar 2012 17:10:53 GMT

Hi All,
For a client I have to make a Master Calendar. I make this for each dashboard so this is not the question. This customer, however, has questioned whether to make a Tertial. A tertial is a period of 4 months. I have succeed in that. However, now the client wants the tertial starts at 1 Sep.

My question is how can I create tertial that will start on 1 September.

Thanks for the help and comments

Create date periode (Tertial) starting at September

Fri, 16 Mar 2012 17:51:43 GMT

How did you build your calendar?

LOAD * INLINE [

monthnumber, tertial

1,2

2,2

3,2

4,2

5,3

6,3

7,3

8,3

9,1

10,1

11,1

12,1

]

Create date periode (Tertial) starting at September

jjordaan — Fri, 16 Mar 2012 22:28:28 GMT

Thanks for your comment.
Below is how I build my calendar.

Min_Max_Dates:

LOAD
MIN(BEGIN_DATE) AS MinDate,
MAX(BEGIN_DATE) AS MaxDate
RESIDENT FinancialYear;

LET varMinDate = Num(Peek('MinDate', 0, 'Min_Max_Dates'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'Min_Max_Dates'));
LET varToday = Num(today());
LET date0 = DATE(0);

//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************Master Calendar************
MasterCalendar:
LOAD
TempDate AS CalendarDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day (TempDate) AS Day,
Dual(weekday(TempDate),Mod(weekday(TempDate)+1,7)) AS WeekDay
'Q' & Ceil(Month(TempDate)/3) AS Quarter,
'T' & Ceil(Month(TempDate)/4) AS Tertial,
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 TempCalendar
Order by TempDate ASC;

Create date periode (Tertial) starting at September

Sat, 17 Mar 2012 17:52:59 GMT

Does the first solution works?

Re: Create date periode (Tertial) starting at September

jjordaan — Sat, 17 Mar 2012 20:39:32 GMT

EvaPOLONI,

Thanks for your help.
With your example I can indeed create a Tertial that begins on 1 September.

But today there is a difficulty level added. The customer has its own financial year which they create themselves from 2003 to 2021 made in Excel. They asked if I can create the Master Calendar from this Excel.

I have an example of a year are added.
Pay particular attention to row 2 and row 53.

Thank you so much for your help.

Create date periode (Tertial) starting at September

Mon, 19 Mar 2012 14:13:26 GMT

Hi,

I tried to execute your script. It doesn't work for me "TempCalendar" can't be created.

Anyway.

In your master calendar, you have to load the weekAbsoluteNumber. (same value in your excel sheet column B).

After you add a statement to load the excel sheet as you call the column 2 :weekAbsoluteNumber.

It should working.

Eva