How to add a field for categorized by Today, MTD, QTD, & YTD

[Sheena_dup_300]

I have a question on how to get a field to return the following values: Today, MTD, QTD, YTD.  

*Where YTD = October 1st of the previous year as the start of the YTD.  For Example: This year's YTD = October 1, 2016 to present.  

If Today = Monday THEN return Friday, Saturday, Sunday = "Today"

If Today is NOT Monday THEN return yesterday = "Today"

THEN return all dates for the month = "MTD" THEN return all dates for the quarter = "QTD"

THEN return all dates for the year = "YTD"  (YTD starts = October 1, 2016)  

The first part of my question (Monday vs the rest of the work week) was answered in a previous post and seems to be working correctly:

IF DateTimeFormat(DateTimeToday(), "%a") = "mon"

THEN DateTimeDiff(DateTimeFormat(DateTimeToday(), "%Y-%m-%d"),DateTimeFormat([OppSystemCloseDate],"%Y-%m-%d"),"days")<4

ELSE DateTimeFormat([OppSystemCloseDate],"%Y-%m-%d") = DateTimeFormat(DateTimeAdd(DateTimeToday(), -1, "days"), "%Y-%m-%d")

ENDIF

[NicoleJohnson · Accepted solution]

MTD:
DateTimeFormat([OppSystemCloseDate],"%m")=DateTimeFormat(DateTimeToday(),"%m")

QTD and YTD:
Set up two new columns to use for your filters, one for fiscal year and one for quarter.

Year:
If ToNumber(DateTimeFormat([OppSystemCloseDate],"%m"))>=10
Then ToNumber(DateTimeFormat([OppSystemCloseDate],"%Y"))+1
Else ToNumber(DateTimeFormat([OppSystemCloseDate],"%Y"))
Endif

Quarter: (basically just need to assign months to quarters 1-4)
Switch(ToNumber(DateTimeFormat([OppSystemCloseDate],"%m")),"0",10,1,11,1,12,1,1,2,2,2,3,2,4,3,5,3,6,3,7,4,8,4,9,4)

Or...

If ToNumber(DateTimeFormat([OppSystemCloseDate],"%m"))<=3
Then 2
Else If ToNumber(DateTimeFormat([OppSystemCloseDate],"%m"))<=6
Then 3
Else If ToNumber(DateTimeFormat([OppSystemCloseDate],"%m"))<=9
Then 4
Else 1 Endif
Endif
Endif

Once you have your Year and Quarter fields, you can then filter on them directly for those that equal the year or quarter that applies to today's date using the DateTimeToday() formula.

Does that accomplish what you're looking for?

NJ

[Sheena_dup_300]

Thank you Nicole!

[zwiskur]

Hi, NicoleJohnson

Please send the formula for MTD data pull. I have a field [Date Closed] and need the data for MTD.

Thank you