Scroll

Custom Function: Month Check

Returns true if the provided date value is within a full specified month, or a specified month to date. Returns false otherwise.

Note: This function is intended to be used for conditional aggregates. If one wanted to calculate the sum of one data field within the bounds of a given month or month to date, they would use this structure:

=AggSum( If( MonthCheck( {dateField}, monthsBack, toDate ), {fieldToSum}, 0))

Arguments

1. dateValue - A date, dateTime, or date string that will be inspected to see if it is within this year to date.

2. monthsBack - Number of months back from current month. A value of 0 would be "this month", 1 would be "last month", etc.

3. toDate - Boolean value, set to true to calculate month to date for the specified month, set to false to use the full month.

Code

//create return value
bool retVal = false;

//get input date
DateTime myDate = DateTime.Parse(args[0].ToString());

//get number of months to go back
int monthsBack = Convert.ToInt32(args[1].ToString());

//subtract appropriate amount of months from today's date
DateTime dateBound = DateTime.Today.AddMonths(-1 * monthsBack);

//check if we want to check month or month to date
bool toDate = Convert.ToBoolean(args[2]);

//create boundary day - this will be last day of month, or current day number if toDate flag is set
int boundaryDay = toDate ? dateBound.Day : DateTime.DaysInMonth(dateBound.Year, dateBound.Month);

//create first and last days of the month to compare to input date
DateTime firstDay= new DateTime(dateBound.Year, dateBound.Month, 1);
DateTime lastDay = new DateTime(dateBound.Year, dateBound.Month, boundaryDay);

//check if input date is within bounds of first and last day
if(myDate >= firstDay && myDate <= lastDay)
{
retVal = true;
}

//return
return retVal;

Hidden Article Information

Article Author
Exago Services
created 2018-08-22 14:10:09 UTC
updated 2018-08-22 14:32:35 UTC

Labels
no labels yet!
Have more questions? Submit a request