-
1. Re: Date Prior and Quarter wise and Month wise
Mavis LiuJun 12, 2018 7:24 AM (in response to veeru G)
Hi Veeru,
This thread may be useful for you: https://community.tableau.com/message/774692?et=watches.email.thread#774692
Thanks,
Mavis
-
2. Re: Date Prior and Quarter wise and Month wise
Zhouyi ZhangJun 12, 2018 7:34 AM (in response to veeru G)
-
Book1_v10.2.twbx 1.2 MB
-
-
3. Re: Date Prior and Quarter wise and Month wise
Mavis LiuJun 12, 2018 7:53 AM (in response to Mavis Liu)
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
-
Dates calc_v10.2.twbx 7.4 MB
-
-
4. Re: Date Prior and Quarter wise and Month wise
veeru G Jun 12, 2018 10:35 PM (in response to veeru G)