How can I create the four seasons in master calendar?

[#1]

Hi all,

How can I create the 4 seasons : Spring, Summer, Fall, Winter master Calendar ?

QuartersMap: 

MAPPING LOAD 

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

              min(OrderDate) as minDate, 

              max(OrderDate) as maxDate 

Resident Orders; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

              $(varMinDate) + Iterno()-1 As Num, 

              Date($(varMinDate) + IterNo() - 1) as TempDate 

              AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

              TempDate AS OrderDate, 

              week(TempDate) As Week, 

              Year(TempDate) As Year, 

              Month(TempDate) As Month, 

              Day(TempDate) As Day, 

              YeartoDate(TempDate)*-1 as CurYTDFlag, 

              YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

              inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

              date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

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

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

              WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

Thanks for your help.

[petter]

It depends a little bit how you want the seasons to span years... winter will end on year and continue the next year.

Pick( Num( Month( TempDate ) ) , 'Wi','Wi','Wi','Sp','Sp','Su','Su','Su','Au','Au','Wi','Wi') AS Seasons

or

Pick( Num( Month( TempDate ) )

,'Winter '&Year( AddMonths( TempDate , -12 ) ) & '-' & Year( TempDate )

,'Winter '&Year( AddMonths( TempDate , -12 ) ) & '-' & Year( TempDate )

,'Winter '&Year( AddMonths( TempDate , -12 ) ) & '-' & Year( TempDate )

,'Spring '& Year( TempDate )

,'Spring '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Autumn '& Year( TempDate )

,'Autumn '& Year( TempDate )

,'Winter '&Year( TempDate ) & '-' & Year( AddMonths( TempDate , -12 ) )

,'Winter '&Year( TempDate ) & '-' & Year( AddMonths( TempDate , -12 ) )

) AS Seasons

[sinanozdemir]

Hi,

You probably thought about this, but you could divide the year into 4 seasons such as September 21 through December 21 is Fall, December 22 through March 21 is Winter, March 22 through June 21 is Spring and June 22 through September 20 is Summer. Just a thought.

[maxgro · Accepted solution]

something like (check the 20,21,22)

MasterCalendar:

Load

  *,

  if(Month=3 and Day>=20 or Month=4 or Month=5 or Month=6 and Day<22, 'Spring',

  if(Month=6 and Day>=22 or Month=7 or Month=8 or Month=9 and Day<=20, 'Summer',

  if(Month=9 and Day>=20 or Month=10 or Month=11 or Month=12 and Day<22, 'Autumn',

  'Winter'

  ))) as Season;

Load

              TempDate AS OrderDate,

              week(TempDate) As Week,

              Year(TempDate) As Year,

              Month(TempDate) As Month,

              Day(TempDate) As Day,

              YeartoDate(TempDate)*-1 as CurYTDFlag,

              YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

              inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

              date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

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

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

              WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[#5]

Thanks you Petter.

[#6]

Thanks Sinan.

Nice approach!!

[#7]

Hi Massimo,

Thanks for your response.

How can I create fields Winter Sales and Summer Sales that represent the sales season 2015 :

• Winter Sales : from 7/01 to 17/02/2015
• Summer Sales : from 24/06 to 4/08/2015

Many thanks for your help.

[#8]

Thanks Massimo I use the same approach to construct Summer and Winter Sales.

[#9]

Thanx Petter, good decision, but I'd like to correct a few, optionally:

Pick( Num( Month( TempDate ) )

,'Winter '&Year( AddMonths( TempDate , -12 ) ) & '-' & Year( TempDate )

,'Winter '&Year( AddMonths( TempDate, -12 ) ) & '-' & Year( TempDate )

,'Spring '& Year( TempDate )

,'Spring '& Year( TempDate )

,'Spring '& Year( TempDate )

,'Autumn '& Year( TempDate )

,'Autumn '& Year( TempDate )

,'Autumn '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Winter '&Year( TempDate ) & '-' & Year( AddMonths( TempDate, 12 ) )

) AS Seasons;