DataEngine supports time series functions useful in business calculations, where time-based summaries need to be created on period-to-date. The PeriodsToDate functions are time series functions that help you conveniently get information for any specific time, be it a month, year, quarter, or a week. 

The DataEngine provides four types of PeriodsToDate functions:

• Ytd (Year to date)
• Mtd (Month to date)
• Qtd (Quarter to date)
• Wtd (Week to date)

Let's consider an example of the Pet License sample (located at the Documents\ComponentOne Samples\ServiceComponents\DataEngine\CS location). This sample uses DataEngine to analyze a dataset of animal pet licenses for the city of Seattle by species, calendar year, specific city names, etc. The PeriodsToDate functions become quite handy here. It makes extracting and filtering the licenses information less tedious and more productive. For instance, you can filter out the licenses data for a particular week, month, quarter, or year within just a couple of minutes.

Let's explore how to use these functions in detail:

Ytd function

The ColumnCondition class provides Ytd (YearToDate) function, which takes the DateTime object as an argument. The function returns a set of data starting from the beginning of the year (to which the month belongs) to the specified date. For example, if you pass "5 August 2017" as the parameter, then the function returns the values starting from the beginning of the year, that is, January 2017 to the specified date, that is, 5 August 2017.

Please note that if the functions are called without an argument, then the end date is DateTime.Now.

Use the below code snippet to retrieve the data, let's say starting from Jan 2017 to 5 August 2017.

// Number of licenses by species, year to date (as of 8/5/2017)
if (!workspace.QueryExists("YearToDate"))
{
    var when = new DateTime(2017, 8, 5);
    
    // Create a query with all base table columns and a range expression with the Ytd operator
    dynamic parent = workspace.query(new {
        _base = "*",
        _range = licenses.IssueDate.Ytd(when)
    });


    // Derive another query from the unnamed query above and perform the aggregation
    dynamic query = workspace.query("YearToDate", new
    {
        Species = parent.Species,
        Count = Op.Count(parent.Species)
    });

    query.Query.Execute();
}

You can also retrieve the data for the Fiscal Year by specifying the FiscalYearFirstMonth property of the Workspace class.

// Number of licenses by species, fiscal year to date (as of 8/5/2017)
if (!workspace.QueryExists("FiscalYearToDate"))
{
    var when = new DateTime(2017, 8, 5);

    // Fiscal year begins in April
    workspace.FiscalYearFirstMonth = 4;
    
    // Create a query with all base table columns and a range expression with the Ytd operator
    dynamic parent = workspace.query(new {
        _base = "*",
        _range = licenses.IssueDate.Ytd(when)
    });

    // Derive another query from the unnamed query above and perform the aggregation
    dynamic query = workspace.query("FiscalYearToDate", new {
        Species = parent.Species,
        Count = Op.Count(parent.Species)
    });

    query.Query.Execute();
}

Qtd function

The ColumnCondition class provides Qtd (QuarterToDate) function, which takes the DateTime object as an argument. The function returns a set of values starting from the beginning of the quarter to which the month belongs to the specified date. For example, if you pass "5 Aug 2017" as the parameter, then the function returns the values starting from the beginning of the third quarter, that is, from July 2017 to the specified date.

Use the below code snippet to retrieve the data starting from July 2017 to August 2017, that is the third quarter.

// Number of licenses by species, quarter to date (as of 8/5/2017)
if (!workspace.QueryExists("QuarterToDate"))
{
    var when = new DateTime(2017, 8, 5);

    // Create a query with all base table columns and a range expression with the Qtd operator
    dynamic parent = workspace.query(new
    {
        _base = "*",
        _range = licenses.IssueDate.Qtd(when)
    });

    // Derive another query from the unnamed query above and perform the aggregation
    dynamic query = workspace.query("QuarterToDate", new
    {
        Species = parent.Species,
        Count = Op.Count(parent.Species)
    });

    query.Query.Execute();
}

Mtd function

The ColumnCondition class provides Mtd (MonthToDate) function takes DateTime object as an argument. The function returns the set of values starting from the beginning of the month to the specified date. For example, if you pass "5 August 2017" as the parameter, then the function returns the set of values starting from the beginning of the month to the specified date.

Use the below code snippet to retrieve the data starting from 1st August 2017 to 5th August 2017.

// Number of licenses by species, month to date (as of 8/5/2017)
if (!workspace.QueryExists("MonthToDate"))
{
    var when = new DateTime(2017, 8, 5);

    // Create a query with all base table columns and a range expression with the Mtd operator
    dynamic parent = workspace.query(new
    {
        _base = "*",
        _range = licenses.IssueDate.Mtd(when)
    });

    // Derive another query from the unnamed query above and perform the aggregation
    dynamic query = workspace.query("MonthToDate", new
    {
        Species = parent.Species,
        Count = Op.Count(parent.Species)
    });

    query.Query.Execute();
}

Wtd function

The ColumnCondition class provides Wtd (WeekToDate) function, which takes the DateTime object as an argument. The function returns a set of values starting from the beginning of the week to the specified date. For example, if you pass "5 August 2017" as the parameter, then the function returns the values starting from the beginning of that week, that is 31st July 2017 to the specified date, that is, 5th August 2017.

Use the below code snippet to retrieve the data of the first week of August 2017.

// Number of licenses by species, week to date (as of 8/5/2017)
if (!workspace.QueryExists("WeekToDate"))
{
    var when = new DateTime(2017, 8, 5);

    // Create a query with all base table columns and a range expression with the Wtd operator
    dynamic parent = workspace.query(new
    {
        _base = "*",
        _range = licenses.IssueDate.Wtd(when)
    });

    // Derive another query from the unnamed query above and perform the aggregation
    dynamic query = workspace.query("WeekToDate", new
    {
        Species = parent.Species,
        Count = Op.Count(parent.Species)
    });

    query.Query.Execute();
}