Calculating Amounts for Relative Date Ranges

You can create formula fields to calculate amounts for relative date ranges. This includes transaction sales amounts, quantities, and other numerical values. Depending on the date range, here are some formulas you can use. The output type for each of these formulas is FLOAT:

Important:

If you're working with values in multiple currencies, make sure you consolidate or convert the field values to a single currency in your formula. For more information, see Currency Consolidation and Conversion Using Custom Formula Fields.

Note:

These samples work for formula fields in datasets based on the transaction record type. If you're working on a dataset based on the sales (ordered) or sales (invoiced) analytical record types, replace {foreigntotal} with {amountnet}. If you'd like to calculate other values, like quantity sold, replace {foreigntotal} with the right field ID.

Amount Last Year to Date
Amount This Year to Date
Amount This Year
Amount Last Year
Amount This Quarter to Date
Amount Last Quarter
Amount This Month to Date
Amount Last Month to Date

After you've created your formulas, you can use them to make a calculated measure and analyze period-over-period variances. For more information, see Year over Year Growth.

Amount Last Year to Date

Use this formula to get transaction amounts from all of last year up to today:

            CASE WHEN (ADD_MONTHS({trandate}, 12) <= CURRENT_DATE) AND (EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE)) THEN TO_NUMBER({foreigntotal})
END

Amount This Year to Date

Use this formula to get transaction amounts from the start of this year up to today:

            CASE WHEN {trandate} <= CURRENT_DATE AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount This Year

Use this formula to get the total amounts for transactions created this year:

            CASE WHEN EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN {foreigntotal}
END

Amount Last Year

Use this formula to get the total amounts for transactions created last year:

            CASE WHEN EXTRACT(YEAR FROM {trandate}) + 1 = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount This Quarter to Date

Use this formula to get amounts for transactions created during this quarter:

            CASE WHEN TO_CHAR({trandate},'Q') = TO_CHAR(CURRENT_DATE,'Q') AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE) THEN TO_NUMBER({foreigntotal})
END

Amount Last Quarter

Use this formula to get transaction amounts from last quarter:

            CASE
 WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) > 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q'))-1 AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
 
 WHEN TO_NUMBER(TO_CHAR(CURRENT_DATE,'Q')) = 1 AND TO_NUMBER(TO_CHAR({trandate},'Q')) = 4 AND EXTRACT(YEAR FROM {trandate})+1 = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END

Amount This Month to Date

Use this formula to get amounts for transactions from the start of this month up to today:

            CASE
 WHEN {trandate} <= CURRENT_DATE AND EXTRACT(Month FROM {trandate}) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM {trandate}) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END

Amount Last Month to Date

Use this formula to get transaction amounts from all of last month up to today:

            CASE
 WHEN (ADD_MONTHS({trandate}, 1) <= CURRENT_DATE) AND EXTRACT(Month FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(Month FROM CURRENT_DATE) AND EXTRACT(YEAR FROM ADD_MONTHS({trandate}, 1)) = EXTRACT(YEAR FROM CURRENT_DATE)
 THEN TO_NUMBER({foreigntotal})
END