4 Replies Latest reply on Jun 12, 2018 10:35 PM by veeru G

# Date Prior and Quarter wise and Month wise

Hi Team,

I have  Date field that contains 01-01-2014 to Till now. I need to show below image. Here Quarter is completed it shows Quarter wise else month wise.

Refer this Date Prior and Quarter wise  In this we are showing Quarter wise even Quarter is not completed.

Thanks Much

Veeru.

• ###### 1. Re: Date Prior and Quarter wise and Month wise

Hi Veeru,

Thanks,

Mavis

• ###### 2. Re: Date Prior and Quarter wise and Month wise

Hi, Veeru

Hope this helps

ZZ

• ###### 3. Re: Date Prior and Quarter wise and Month wise

Hi Veera,

Not a very nice calculation, but this should work out if it's the final day of the quarter, then it will be as a quarter. Otherwise it'll be month:

IF  YEAR([NEW ORDER DATE]) <= YEAR(DATEADD('year',-2,TODAY()))

ELSEif

DATETRUNC('quarter',[NEW ORDER DATE]) < DATETRUNC('quarter',TODAY())

then

str( YEAR([NEW ORDER DATE])) + ' Q' +

str( DATEPART('quarter', [NEW ORDER DATE]))

elseif DATETRUNC('quarter',[NEW ORDER DATE]) = DATETRUNC('quarter',TODAY())

and DATETRUNC('quarter',DATEADD('day',1, [NEW ORDER DATE (Quarters)])) = DATETRUNC('quarter',TODAY())

then

str( datename('month',[NEW ORDER DATE])) + ' ' +

str( DATEPART('year', [NEW ORDER DATE]))

elseif DATETRUNC('quarter',[NEW ORDER DATE]) = DATETRUNC('quarter',TODAY())

and DATETRUNC('quarter',DATEADD('day',1, [NEW ORDER DATE])) > DATETRUNC('quarter',TODAY())

then

str( YEAR([NEW ORDER DATE])) + ' Q' +

str( DATEPART('quarter', [NEW ORDER DATE]))

elseif [NEW ORDER DATE]>TODAY()then

'Future'

END

So in our case, as our quarter hasn't finished then it is not 2018 Q2 yet. However when we hit the 30th of June, then it will update ti 2018 Q2.

Thanks,

Mavis

• ###### 4. Re: Date Prior and Quarter wise and Month wise

Thanks Zhouyi Zhang & Mavis. Both of the solutions working fine.