topic Re: Quarter Year conversion creating dates with decimals in App Development

Quarter Year conversion creating dates with decimals

aswinnar — Sat, 16 Nov 2024 01:14:29 GMT

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.

Re: Quarter Year conversion creating dates with decimals

Kushal_Chawda — Mon, 26 Oct 2020 23:02:37 GMT

@aswinnar try below

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

Re: Quarter Year conversion creating dates with decimals

aswinnar — Mon, 26 Oct 2020 23:10:19 GMT

Hi @Kushal_Chawda,

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

Re: Quarter Year conversion creating dates with decimals

Kushal_Chawda — Mon, 26 Oct 2020 23:18:05 GMT

@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'))

Re: Quarter Year conversion creating dates with decimals

aswinnar — Tue, 27 Oct 2020 00:19:32 GMT

Hi @Kushal_Chawda ,

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