Create date periode (Tertial) starting at September

[jjordaan]

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

[#2 · Accepted solution]

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

]

[jjordaan]

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;

[#4]

Does the first solution works?

[jjordaan]

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.

[#6]

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

/