Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

balrajprabhu
Not applicable

Quarters in Master calender

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
Tags (3)
1 Solution

Accepted Solutions
swarup_malli
Not applicable

Re: Quarters in Master calender


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

2 Replies
swarup_malli
Not applicable

Re: Quarters in Master calender

Try this:

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


swarup_malli
Not applicable

Re: Quarters in Master calender


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