Make quarter date from script attached

[rcorcoran]

Hi below is the part of the script where I have made a date YearMonth from ReferredMM and ReferredYYYY.  I also need to make a quarter date field which I was trying below but not getting very far with. 

Any help would be greatly appreciated

FACTS:
LOAD  ActualValue,
ReferredMM,
ReferredYYYY,
MonthName(Date(makedate(ReferredYYYY,ReferredMM))) as YearMonth,
// 'Q' &Ceil(MonthName(Date(makedate(ReferredYYYY,(ReferredMM/3))))) as Quarter,
 

[sunny_talwar]

Try this for quarter:

'Q' & Ceil(Month(MakeDate(RefferedYYYY, ReferredMM, 1))/3) as Quarter

Update: Just realized that I missed a parenthesis

[rcorcoran]

Hi Sunny T

Thanks for answering but it didn't work maybe because ReferredMM and ReferredYYYY are not date fields?

Thanks

[sunny_talwar]

Can you share the data within those two fields?

[rcorcoran]

Yes see above where I have made the month and year into amalgamated date

[jonathandienst]

Assuming ReferredMM is a text month:

     Ceil(Month(Date#(ReferredYYYY & Left(ReferredMM, 3), 'YYYYMMM')) / 3) As Quarter

[sasiparupudi1 · Accepted solution]

Please try

='Q' & Ceil(Month(MakeDate(ReferredYYYY, ReferredMM, 1))/3)

hth

Sasi

[rcorcoran]

Thank you so much this has worked perfectly.

Rhona

[rcorcoran]

Thanks Sunny also right.  Thanks for your help