topic How do I concatenate a quarter and year column and convert into date? in App Development

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

jleefjcapital — Wed, 05 Apr 2017 20:29:19 GMT

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?

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

sunny_talwar — Wed, 05 Apr 2017 21:04:27 GMT

May be this

"Year" as YEAR,

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

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

jleefjcapital — Wed, 12 Apr 2017 18:37:32 GMT

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

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

sunny_talwar — Wed, 12 Apr 2017 18:43:20 GMT

May be this

"Year" as YEAR,

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

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

nitesh2730 — Tue, 23 Feb 2021 10:05:42 GMT

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] )

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

MayilVahanan — Tue, 23 Feb 2021 10:15:49 GMT

Hi @nitesh2730

Try like below

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

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

nitesh2730 — Tue, 23 Feb 2021 10:20:58 GMT

not working, picking up only latest month

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

MayilVahanan — Tue, 23 Feb 2021 10:26:56 GMT

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] )

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

nitesh2730 — Tue, 23 Feb 2021 10:30:39 GMT

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