Quarter Year conversion creating dates with decimals

[aswinnar]

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. 

[Kushal_Chawda]

@aswinnar  try below

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

[aswinnar]

Hi @Kushal_Chawda, 

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

[Kushal_Chawda · Accepted solution]

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

Hi @Kushal_Chawda , 

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