topic Re: Quarters in Master calender in QlikView

Quarters in Master calender

Anonymous — Thu, 12 Feb 2015 20:51:55 GMT

I found the following routine to create a master calendar written by Rob Wunderlich.

When I try to tweak it to create quarters ,it fails ( I have highlighted the code in black ).

Need assistance with creating quarters using this routine

//===================================================
// Subroutine to Generate Calendar.
//===================================================
SUB CalendarFromField(_field, _calendar, _prefix)
[$(_calendar)]:
// Generate Final Calendar
Load
'Q' & ceil((mod((Month([$(_field)])+8),12)+1)/3) as[$(_prefix)Quarter1],
'Q' & pick(Month([$(_field)]),4,4,4,1,1,1,2,2,2,3,3,3)as[$(_prefix)Quarter] ;
LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
// 'Q' & ceil(month([$(_field)]) / 3) as[$(_prefix)Quarter]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB

Re: Quarters in Master calender

swarup_malli — Thu, 12 Feb 2015 21:14:48 GMT

Try this:

'Q' & ceil(Num(month([$(_field)]))/3) as [$(_prefix)Qtr];

Re: Quarters in Master calender

swarup_malli — Thu, 12 Feb 2015 21:19:04 GMT

LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday],
'Q' & ceil(Num(month([$(_field)]))/3) as [$(_prefix)Qtr]
;
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
// Find min and max of date field values.
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
// Load date field values.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');

END SUB