5 Replies Latest reply: Mar 19, 2012 10:13 AM by Eva Polini

# Create date periode (Tertial) starting at September

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

How did you build your calendar?

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

Below is how I build my calendar.

Min_Max_Dates:

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:
\$(varMinDate) + rowno() - 1 AS DateNumber,
date (\$(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
\$(varMaxDate) - \$(varMinDate) + 1;

//*************Master Calendar************
MasterCalendar:
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

Does the first solution works?

• ###### Re: Create date periode (Tertial) starting at September

EvaPOLONI,

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

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

/