How do I concatenate a quarter and year column and convert into date?

[jleefjcapital]

I'm trying to concatenate a row containing the quarter (Q1, Q2, Q3, Q4) and year (1990,1991,...) and turn it into a date.  I tried using the following code:

Date(Makedate("Year"), 'YYYY') as YEAR,

Date(Makedate("Year",right(Qrt,2)), 'QQYYYY') as QuarterYEAR,

It's not working as intended.  What am I doing wrong?

[sunny_talwar]

May be this

"Year" as YEAR,

Date(MakeDate("Year", (Right(Qrt, 1) * 3)), 'MMYYYY') as MonthYEAR,

[jleefjcapital]

It worked!  It is possible to get the add a "/"?   Currently, format is '032013'. 

[sunny_talwar]

May be this

"Year" as YEAR,

Date(MakeDate("Year", (Right(Qrt, 1) * 3)), 'MM/YYYY') as MonthYEAR,

[nitesh2730]

Hi @sunny_talwar ,

i also have a same type of query, i have used the above expression which works well, however when I use this for MAT calculation it's changing the date format below is ex.

=Sum({<Year_Quarter={>=$(=(Max(Year_Quarter)-4))<=$(=(Max(Year_Quarter)))}>} [Market Units] )

[MayilVahanan]

Hi @nitesh2730 

Try like below

=Sum({<Year_Quarter={">=$(=Date(Max(Year_Quarter)-4),'MM/YYYY')<=$(=Date(Max(Year_Quarter),'MM/YYYY'))"}>} [Market Units] )

[nitesh2730]

not working, picking up only latest month

[MayilVahanan]

Hi @nitesh2730 

Sorry, wrongly placed the closing bracket.

=Sum({<Year_Quarter={">=$(=Date(Max(Year_Quarter)-4,'MM/YYYY'))<=$(=Date(Max(Year_Quarter),'MM/YYYY'))"}>} [Market Units] )

[nitesh2730]

Sorry to trouble your again, still there is some problem, am using -4 its picking up 08 month only, i tried to change it -1,-2,-3 but its showing 08 month