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?
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';
the month is in numbers: I used the below script
It gives me all except Q4 Data. eg: Q12017,Q22017,Q32017,Q12016,Q22016,Q32016. It is dropping Q4 data
try 3 instead of 4
'Q' & Ceil(Month / 3) & Year
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
Retrieving data ...