Get QuarterYear field from Month and Year

[Anonymous]

Hi all,

I have a dataset in the below format where I have got only "Month" and "Year" fields. The QuarterYear is what I want.

How can I get the "QuarterYear" field using Month and Year field?

[jonathandienst]

If Month is numeric (or dual):

'Q' & Ceil(Month / 4) & Year

If Month is a string:

'Q' & Ceil(Index(MonthNames, Month) / 4 / 4) & Year

(MonthNames is the system environment variable containing short month names)

Set MonthNames = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

[pradosh_thakur]

if just string/text

'Q' & Ceil(pick(match(Month,'Jan','Feb','Mar','Apr,........etc   till 'Dec'),1,2,3,4,5,6,7,8,9,10,11,12) / 3) & Year

regards

Pradosh

[Anonymous]

the month is in numbers: I used the below script

If Month is numeric (or dual):

'Q' & Ceil(Month / 4) & Year

It gives me all except Q4 Data. eg: Q12017,Q22017,Q32017,Q12016,Q22016,Q32016. It is dropping Q4 data

[pradosh_thakur · Accepted solution]

try 3 instead of 4

'Q' & Ceil(Month / 3) & Year