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.

Not applicable

Reporting using quarters when 1st quarter starts in March?

How can I modify the usual script method for defining quarters (below)  so that it works when the first quarter starts in March? (ie Jan and Feb are Q4?)

QuartersMap:

Mapping LOAD

RecNo() as Month,

'Q' & ceil(recno()/3) as Quarter

AutoGenerate 12;

Wondering if there is a quick workaround just by defining a map so that I list  1 = Q4, 2= Q4, 3=Q1, 4= Q1 etc, but I can't find the syntax.

Any help much appreciated

1 Solution

Accepted Solutions
Not applicable

Re: Reporting using quarters when 1st quarter starts in March?

Hi Robert,

You can use the method mentioned in attached sample application.

Script:

QuartersMap:

Mapping

LOAD * INLINE

[Month, Qtr

1, Q4

2, Q4

3, Q1

4, Q1

5, Q1

6, Q2

7, Q2

8, Q2

9, Q3

10, Q3

11, Q3

12, Q4

];

MyTable:

LOAD RecNo() AS Mth,

ApplyMap('QuartersMap',RecNo()) AS Qtr

AutoGenerate(12);

The other programatic way is below:

MyTable:

LOAD RecNo() AS Mth,

Pick(Match(RecNo(),1,2,3,4,5,6,7,8,9,10,11,12), 4,4,1,1,1,2,2,2,3,3,3,4) AS Qtr

AutoGenerate(12);

Please revert back for any further query.

Cheers,

Dhananjay (DJ)

5 Replies
MVP
MVP

Reporting using quarters when 1st quarter starts in March?

Well, if you like to use something like a lookup or mapping, you could probably do

'Q' & pick(recno(),4,4,4,1,1,1,2,2,2,3,3,3) as Quarter

or maybe without a lookup

'Q' & if( ceil(recno()/3)-1=0,4,ceil(recno()/3) ) as Quarter

Hope this helps,

Stefan

Not applicable

Re: Reporting using quarters when 1st quarter starts in March?

Hi Robert,

You can use the method mentioned in attached sample application.

Script:

QuartersMap:

Mapping

LOAD * INLINE

[Month, Qtr

1, Q4

2, Q4

3, Q1

4, Q1

5, Q1

6, Q2

7, Q2

8, Q2

9, Q3

10, Q3

11, Q3

12, Q4

];

MyTable:

LOAD RecNo() AS Mth,

ApplyMap('QuartersMap',RecNo()) AS Qtr

AutoGenerate(12);

The other programatic way is below:

MyTable:

LOAD RecNo() AS Mth,

Pick(Match(RecNo(),1,2,3,4,5,6,7,8,9,10,11,12), 4,4,1,1,1,2,2,2,3,3,3,4) AS Qtr

AutoGenerate(12);

Please revert back for any further query.

Cheers,

Dhananjay (DJ)

Not applicable

Re: Reporting using quarters when 1st quarter starts in March?

Many thanks for your help - Robert

Not applicable

Re: Reporting using quarters when 1st quarter starts in March?

Great – thanks for your held Dhannanjay

Not applicable

Re: Reporting using quarters when 1st quarter starts in March?

Thanks for your help Swuehl