A Guide to Daily Data Aggregation in Electronic Reporting

From time to time, I need to develop reports that present monthly data aggregated by day. In this post, I’ll demonstrate how a user can select a specific month of the year and generate a CSV report showing the total vendor invoiced amount for each day. I’ll also walk through the methodology I use and address key challenges, such as determining the first and last days of a given month.

Model

I’m keeping the example as simple as possible:

Mapping

We’ll create two user input parameters: one for the month and another for the year, using system EDTs. Additionally, I’ll declare the MonthsOfYear enum:

Now, we’ll transform the month from an enum to an integer using a calculated field called $MonthInt:

CASE
(
    Month,
    MonthsOfYear.January,1,
    MonthsOfYear.February, 2,
    MonthsOfYear.March, 3,
    MonthsOfYear.April, 4,
    MonthsOfYear.May, 5,
    MonthsOfYear.June, 6,
    MonthsOfYear.July, 7,
    MonthsOfYear.August, 8,
    MonthsOfYear.September, 9,
    MonthsOfYear.October, 10,
    MonthsOfYear.November, 11,
    12
)

Next, we’ll create another calculated field to determine the date of the first day of the month, ensuring the month value is always two digits:

DATEVALUE
(
    "01"&RIGHT("0"&TEXT('$MonthInt'),2)&TEXT(Year),
    "ddMMyyyy"
)

The last day of the month is a bit trickier, but there’s a simple workaround: we calculate the first day of the next month and subtract one day. The formula becomes more complex because we can’t directly subtract from a date. Instead, we need to use a datetime input, requiring us to convert the formula back and forth:

DATEVALUE
(
    DATETIMEFORMAT
    (
        ADDDAYS
        (
            DATETODATETIME
            (
                DATEVALUE
                (
                    "01"&RIGHT("0"
                    &TEXT(
                        IF('$MonthInt'= 12, 1, '$MonthInt'+ 1)
                        ),2)
                        &IF('$MonthInt'= 12, TEXT(Year + 1), TEXT(Year)),
                    "ddMMyyyy"
                )
            )
        ,-1),
    "ddMMyyyy"),
"ddMMyyyy")

Next, we can declare the table records for VendInvoiceJour and filter them by date:

FILTER
(
    VendInvoiceJour,
    AND
    (
        VendInvoiceJour.InvoiceDate >= '$FirstDay',
        VendInvoiceJour.InvoiceDate <= '$LastDay'
    )
)

Finally, we can test our mapping in the previous form (back arrow) before completing it, selecting a month and a year:

Format

We’ll create a format of type Text:

Now, in the mapping of the format under the Model node, let’s create a list with as many records as there are days in the month using this trick:

REPEAT("",DAYS(@.ToDate, @.FromDate))

And for each record, let’s create a field with its date. Again, we have to go back and forth because ADDDAYS doesn’t accept dates, only datetime:

DATEVALUE(
    DATETIMEFORMAT(
        ADDDAYS
        (
            DATETODATETIME(model.FromDate), 
            model.'$Days'.Number - 1
        )
    , "yyyyMMdd")
, "yyyyMMdd")

Now, let’s group the invoices by day using a Group By function and aggregate the amounts:

And in the $Days record for each date, let’s find the corresponding record from $InvoicesGrouped:

FIRSTORNULL(
WHERE
(
    model.'$InvoicesGrouped',
    model.'$InvoicesGrouped'.grouped.Date = model.'$Days'.'$Date'
)
).aggregated.Amount

Now, we can design the structure of the CSV, ensuring the use of New Line as the separator in the NewLines sequence and ‘,‘ in the header and lines. Additionally, we can customize the extension in Format > Custom Extension and set the file name in Mapping > File Name. Finally, we can Run the format: