+ Start a Discussion
tmbarrytmbarry 

Determine what Qtr something was created?

I need to be able to run a report, based on the Current Qtr, that lists all the Oppty created in current qtr or previous one.  

For example: if I run a report today (11/9/15 - Qtr4) the report needs to show eveything created since Aug.  I was thinkning about creating a custome formula field on the Opportunity record with an output of a checkbox with a simple True/False result, but i can not come up with the logic. 

Any thoughts?
Best Answer chosen by tmbarry
tmbarrytmbarry
I think I figured it out...
 
Or(

If(CEILING(MONTH(Today())/3) = 4,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=3 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 3,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=2 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 2,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,      
     If( Year(Today())-1= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =4 , True,False),

False)

)

This was done in a Custom Formula box with a Formula Return Type = Checkbox and a Field Label = Added this Qtr.  Now I can report a report where Added this Qtr = True.
 

All Answers

Mathew Andresen 5Mathew Andresen 5
You can do this using the date functions in the query.


https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm

for example something like 
[SELECT Name, Id FROM Opportunity Where closedate = THIS_QUARTER]

 
Mathew Andresen 5Mathew Andresen 5
also this is helpful
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
 
tmbarrytmbarry
Thank you Matt, but I was hoping to do this with a custom formula field and report and not code. 
 
tmbarrytmbarry
I think I figured it out...
 
Or(

If(CEILING(MONTH(Today())/3) = 4,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=3 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 3,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=2 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 2,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) >=1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,      
     If( Year(Today())= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =1 , True,False),

False)
,
If(CEILING(MONTH(Today())/3) = 1,      
     If( Year(Today())-1= Year(Datevalue(CreatedDate)) && CEILING(MONTH(datevalue(CreatedDate))/3) =4 , True,False),

False)

)

This was done in a Custom Formula box with a Formula Return Type = Checkbox and a Field Label = Added this Qtr.  Now I can report a report where Added this Qtr = True.
 
This was selected as the best answer
Mathew Andresen 5Mathew Andresen 5
Sorry my mistake.

You know you can group by quarter in a report.  For example, select close date, then group by this field, then group dates by and select quarter