Saved Search to Count Records Created by Month

The above is created from a Saved Search and added to our Dashboard in a portlet for quick visibility. Something like this can come in handy when looking at essentially any type of record.

The formula that we would use is:

DECODE (to_char({datecreated}, ‘MM’), ’01’ , 1)

This is in the most basic form and can be modified to fit your needs. You can drill down to the specific month as a whole or as minuscule as date of the month.

Something to note is that this can also be used on the dates of the System Notes. Change the {datecreated} to {systemnotes.date}. Be sure that your initial criteria is set to look directly at the system notes to prevent any funny business.

Date FormatsDate in FormulaExpected Date LongExpected Date Short
MM01January1
MMDD0101January 11/1
MMDDYY010119January 1, 20191/1/19
MMDDYYYY01012019January 1, 20191/1/2019

Select Transaction (or your desired record type)

Name your search in the Search Title field.

In the Criteria tab > Standard Subtab add the filters as needed:
Type is Opportunity (or your desired transaction type)

On the Results tab > Columns Subtab, set the following:

FieldSummary TypeFormulaCustom Label
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’01’ , 1)January
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’02’ , 1)February
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’03’ , 1)March
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’04’ , 1)April
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’05’ , 1)May
Formula (Numeric)SumDECODE (to_char({datecreated}, ‘MM’), ’06’ , 1)June

Duplicate these in the Columns Subtab for as few or many Months/Dates you would like to count. Refer to the table above to see how to break these counts down by specific dates.

When you’re finished, Save and Run!

Leave a Reply

Your email address will not be published. Required fields are marked *