Reporting using quarters when 1st quarter starts in March?

[#1]

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

[swuehl]

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

[#3 · Accepted solution]

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)

[#4]

Many thanks for your help - Robert

[#5]

Great – thanks for your held Dhannanjay

[#6]

Thanks for your help Swuehl