Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator II

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

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?

8 Replies
sunny_talwar

May be this

"Year" as YEAR,

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

jleefjcapital
Creator II
Creator II
Author

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
Contributor II
Contributor II

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

 

nitesh2730_0-1614074707897.png

 

MayilVahanan

Hi @nitesh2730 

Try like below

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

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
nitesh2730
Contributor II
Contributor II

nitesh2730_0-1614075627138.png

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
nitesh2730
Contributor II
Contributor II

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

nitesh2730_0-1614076166241.png