Jump to content

# Calculating Year to date purchases

This topic is 7059 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

• Newbies

I need help to calculate a year to date purchse field any help would be greatly appreciated

Cole

##### Share on other sites

Can you give a bit more information about how your file is set up?

##### Share on other sites

• 2 weeks later...
• Newbies

I have a invoice system where customers are in one database and the invoice info is in another and I would like to be able to look at a customers name and have it calculate the year to date purchases from the invoice database. Thanks so much

##### Share on other sites

You will have these fields in the invoice table:

InvoiceDate - Date of invoice

InvoiceAmount - Amount of purchase

CustomerID - unique ID number of customer

You will also have CustomerID in the customers table.

In your customer table you will need to create an unstored calculation field YearStart with this formula:

Date(1;1;Year(Get(CurrentDate)))

Next, make a relationship named YTD from the customer table to the invoices table with the following match fields

Customers::YearStart <= Invoices::InvoiceDate

AND

Customers::CustomerID = Invoices::CustomerID

Then make a new calculated field in Customers, YTDTotal with the formula:

Sum(YTD::InvoiceAmount)

This should give you your year to date totals by customer

##### Share on other sites

This topic is 7059 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

## Sign in

Already have an account? Sign in here.

Sign In Now
×
×
• Create New...

## Important Information

By using this site, you agree to our Terms of Use.