tableSumIfs

Perform SUMIFS on any table in the model.

tableSumIfs(
    tableName,
    sumColumnName,
    criteriaCol1="", criteria1="",
    criteriaCol2="", criteria2="",
)

Arguments

tableName

The name of the table containing the column to sum. Here you should use a function that ensures that the table exists in the output and returns a table name, like table, excelTable, or csvToTable.

sumColumnName

The name of the column to sum.

criteriaCol1, criteria1, etc.

Optional. See documentation for SUMIFS function.

Changes from Autory 2 to Autory 3

The old csvFetch and csvSumIfs functions were removed in Autory 3. Load the CSV with csvToTable, then pass that table to tableSumIfs.

Old csvFetch formula:

csvFetch(".\model-points", "claims.csv", "ClaimAmount", "SELECT * FROM tbl", "PolicyID", PolicyID)

Old csvSumIfs formula:

csvSumIfs(".\model-points", "claims.csv", "ClaimAmount", "SELECT * FROM tbl", "PolicyID", PolicyID)

New formula:

tableSumIfs(
    csvToTable(path(".\model-points", "claims.csv"), "SELECT * FROM tbl"),
    "ClaimAmount",
    "PolicyID",
    PolicyID
)

Examples

Sum a whole column

To sum the ClaimAmount column from the t_Claims table:

tableSumIfs(
    table("t_Claims"),
    "ClaimAmount",
)

When no criteria are supplied, Autory writes an Excel SUM formula over the requested column.

Sum with criteria

To sum only open claims for the current product code:

tableSumIfs(
    table("t_Claims"),
    "ClaimAmount",
    "Status",
    "Open",
    "ProductCode",
    ProductCode,
)

The criteria arguments are supplied in pairs:

• the criteria column name
• the criteria value

Autory writes an Excel SUMIFS formula and includes t_Claims in the output workbook for the projection node.