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 In this we are showing Quarter wise even Quarter is not completed.
Thanks Much
Veeru.
Jun 12, 2018 7:24 AM
Hi Veeru,
This thread may be useful for you:
Thanks,
Mavis
Jun 12, 2018 7:34 AM
Hi, Veeru
Please find my solution attached.
Hope this helps
ZZ
Jun 12, 2018 7:53 AM
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()))
THEN STR(YEAR(DATEADD('year',-2,TODAY())))+' and Prior'
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