Solved

# Formula to get \$ sum between two dates

• 7 replies
• 45 views

Userlevel 1
• Freshman I
• 17 replies

On a generic enquiry, how do i get the number/\$ sum of invoices etc between to dates?

I have seen the threads as to date differnce calculations, but they all seem to be related to getting the amount of days bewteen two dates.  DateAdd( date, interval, number ) etc

I want to get for example how much a customer spent in 2021 in a column.

icon

Best answer by lauraj46 27 March 2024, 23:47

View original

### 7 replies

Userlevel 1

Hi @dan_00 ,

You could create a formula field on the Results tab similar to this:

=iif(year([ARInvoice.InvoiceDate])=2021, [ARInvoice.CuryOrigDocAmt], 0)

Sum in the Aggregate function column

In the Grouping tab, group on Customer ID

Hope this helps!

Laura

THANKYOU!!!!!!!!!!

Userlevel 7
+9

@dan_00 Please find the attached Generic Inquiry that you can import on your Generic Inquiry Screen.

Thanks

Chandra

Userlevel 7
+9

@dan_00 Here is the results of the Generic Inquiry.

Userlevel 7
+9

Hi @dan_00 Please find the details with Screenshots to guide you.

Create a generic Inquiry as below:

1.  Add Parameters FromDate and ToDate
1. In the Conditions, Add Docdate between [Fromdate] (in the Value 1 box) and [ToDate] (in the Value2 box)

1. In the results grid,
2. Add the OrigDocumentTotal. On this row, in the Aggregate Function column select SUM.
Userlevel 7
+8

Hi @dan_00 ,

The method that @ChandraM describes will give you totals for a date range.  If you need to have columns for multiple periods such as different years then you can use a formula like the one that I shared.

Hope this helps!

Laura

Userlevel 7
+9

Hi @dan_00 Create a generic Inquiry as below:

3. Add Parmeters FromDate and ToDate
4. In the Conditions, Add Docdate between [Fromdate] (in the Value 1 box) and [ToDate] (in the Value2 box)
5. In the results grid,
2. Add the OrigDocumentTotal. On this row, in the Aggregate Function column select SUM.
Userlevel 7
+8

Hi @dan_00 ,

You could create a formula field on the Results tab similar to this:

=iif(year([ARInvoice.InvoiceDate])=2021, [ARInvoice.CuryOrigDocAmt], 0)

Sum in the Aggregate function column

In the Grouping tab, group on Customer ID

Hope this helps!

Laura