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: 
aswinnar
Partner - Contributor III
Partner - Contributor III

Quarter Year conversion creating dates with decimals

Hi Everyone, 

In my app we have a date field that I am needing to convert to a Quarter Year format

so for a date of 2019 - 05 - 01 - 00:00:00 

I am expecting to get a value of Q2-2019

However based on the expression I have, I am getting Q1.6666666666667 - 2019 

 

The expression I am using: 

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11),'MM)))/3&'-'&Date(left(DATEFIELD,11),'YYYY'))

 

Is there something I am doing wrong in my expression. There are some values where I am getting whole numberd for the quarter dates, but most of the values seem to be having the quarter date in decimals. 

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

@aswinnar  make sure that your DATEFIELD is in proper date format. Based on DATEFIELD you can set the below variables in main tab

SET DateFormat='YourDATEFIELDFormat';  
SET TimestampFormat='YourDATEFIELDFormat';

or else I will try to modify your expression like below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11)))/3)&'-'&Date(left(DATEFIELD,11),'YYYY'))

View solution in original post

4 Replies
Kushal_Chawda

@aswinnar  try below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(floor(DATEFIELD))/3)&'-'&year(floor(DATEFIELD))

aswinnar
Partner - Contributor III
Partner - Contributor III
Author

Hi @Kushal_Chawda

 

That expression doesn't seem to be working. I am getting Q- as the result

Kushal_Chawda

@aswinnar  make sure that your DATEFIELD is in proper date format. Based on DATEFIELD you can set the below variables in main tab

SET DateFormat='YourDATEFIELDFormat';  
SET TimestampFormat='YourDATEFIELDFormat';

or else I will try to modify your expression like below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11)))/3)&'-'&Date(left(DATEFIELD,11),'YYYY'))

aswinnar
Partner - Contributor III
Partner - Contributor III
Author

Hi @Kushal_Chawda , 

The second expression is working for me. Thanks for the help!