topic Re: How can I create the four seasons in master calendar? in QlikView

How can I create the four seasons in master calendar?

Sat, 05 Sep 2015 21:10:11 GMT

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.

Re: How can I create the four seasons in master calendar?

petter — Sat, 05 Sep 2015 21:42:59 GMT

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

Pick( Num( Month( TempDate ) )

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

,'Spring '& Year( TempDate )

,'Summer '& Year( TempDate )

,'Autumn '& Year( TempDate )

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

) AS Seasons

Re: How can I create the four seasons in master calendar?

sinanozdemir — Sat, 05 Sep 2015 21:47:51 GMT

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.

Re: How can I create the four seasons in master calendar?

maxgro — Sat, 05 Sep 2015 22:02:19 GMT

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;

Re: How can I create the four seasons in master calendar?

Sat, 05 Sep 2015 22:13:51 GMT

Thanks you Petter.

Re: How can I create the four seasons in master calendar?

Sat, 05 Sep 2015 22:14:43 GMT

Thanks Sinan.

Nice approach!!

Re: How can I create the four seasons in master calendar?

Sat, 05 Sep 2015 22:20:40 GMT

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.

Re: How can I create the four seasons in master calendar?

Sat, 05 Sep 2015 22:32:26 GMT

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

Re: How can I create the four seasons in master calendar?

Sun, 01 Nov 2015 10:02:57 GMT

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 )

,'Autumn '& Year( TempDate )

,'Summer '& Year( TempDate )

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

) AS Seasons;