1 2 Previous Next 15 Replies Latest reply on Mar 2, 2012 1:40 AM by Claw'd

# Calculate total sales in current calander year

• ###### 1. Re: Calculate total sales in current calander year

The cleanest method would be to add the sales to a field every time you make one, and deduct, if you had to refund the funds.you could have a global number field that you add to.

Now, if this is not an option because you want to tally up something that's already there and not going forward, you could create a calculation. You would need to link to the sales table via the sale date to another table that has the current date and the first date of the year. Then sum the sales amounts from the second table.

Hope thus helps.

Best,

Agi

• ###### 2. Re: Calculate total sales in current calander year

Assuming that every sale is a separate record and they are all stored in the same table, wouldn't a simple list view with a summary field do the trick? The perform find for date range could be scripted. This would provide the option to look at sales not only for the calendar year, but by month, week, day, random dates - and provide lot's of options for reporting with sub-summaries.

• ###### 3. Re: Calculate total sales in current calander year

noushie wrote:

Either find all sales in the current year and use a summary field to produce the total, or define a relationship to show only records from current year and a calculation field to sum them.

• ###### 4. Re: Calculate total sales in current calander year

Create these fields:

Year = Year(DateCreated) or InvoiceDate

ProductIDPlusYear = Product ID & "T" & GetAsNumber(Year)

Where ProductID is a unique identifier for each unique part sold

ProductIDPlusCurrentYear = Product ID & "T" & Year(Get( CurrentDate))

Create relationship based on the two fields above

called say: RetailSparesSaleList_ProductIDPlusYear

Change the name to suit your current table name, mine is called RetailSparesSalesList and this is the line item table for the RetailSparesSalesInvoices Table

If you only have one table with the invoiced sales items in use that.

TotalSales = InvQuantity * SoldPrice

YearSalesByPart = Sum(RetailSparesSaleList_ProductIDPlusYear::TotalSales)

This gives a rolling twelve months that automatically gives sales in the calendar year. the downside is that the more sales the slower the calculation but in the main it works reasonably well.

Otherwise use the summary based on a date range as mentioned by Michael Huber.

• ###### 5. Re: Calculate total sales in current calander year

He wanted ALL sales not sales per product.

• ###### 6. Re: Calculate total sales in current calander year

Yes, what I've proposed does just that assuming that the sales have a unique ID per item which they should if the database is set up properly

• ###### 7. Re: Calculate total sales in current calander year

noushie I think you need to tell us more about the DB structure: where the sales are kept, are they in one table, in one file or scattered about the place:)

• ###### 8. Re: Calculate total sales in current calander year

Every sale is a separate record and they are all stored in the same table. I want to have a field at the base of a portal summarising total sales for that client in the current year. I already have a Total field summarising total sales made over the life of the clients relationship with the company.

• ###### 9. Re: Calculate total sales in current calander year

How is the portal displaying the records?

Is it all one customers records, all customer records, records for a calendar year for one customer and what relationship are you using to display the portal records. this info will help to get the best solution.

• ###### 10. Re: Calculate total sales in current calander year

Sorry, I misunderstood, you are correct another sum would be neede to add up all the totals over the entire year

• ###### 11. Re: Calculate total sales in current calander year

Assuming a stored CustomerID in the Invoice Table.

Calculated fields: 'CIDPusYear' = CustomerID & " " & Year(CurrentDate) Unstored, 'CIDPusYearSales' = CustomerID & " " & Year(InvoiceDate) Stored

Self Join Relationship between the two fields in the Invoice Table (CustIDToInvoiceIncDate)

Calculated Field: sum(CustIDToInvoiceIncDate::InvoiceTotal) In the Invoice Table

New layout in Invoice Table with a portal from the new relationship, show whichever fields you want from the Invoice Table

Place the Sum(CustIDToInvoiceIncDate::InvoiceTotal) The field will show the rolling year sales for each customers record you are on.

To smarten it up you could have the 'CustomerID & " " & Year(CurrentDate)' field reference a global field instead of the Year(CurrentDate) containing a drop down of years then it would be easy to compare by changing the year in the global.

Assuming Fields called CustomerID and InvoiceTotal, change to suit.

Haven't tried it with lots of records and there are probably better ways so see how it works for you.

• ###### 12. Re: Calculate total sales in current calander year

Since version 7, you don't need to concatenate matchfields. A relationship defined as:

Clients::ClientID = Sales::ClientID

AND

Clients::cCurrentYearStart < Sales::Date

will work just as fine.

---

cCurrentYearStart would be an unstored calculation field (result is Date) =

Get (CurrentDate) - DayOfYear ( Get (CurrentDate) )

• ###### 13. Re: Calculate total sales in current calander year

Old Habits:)

• ###### 14. Re: Calculate total sales in current calander year

Sorry, I'm not quite sure which fields not to concatenate. Which does the above relationship replace?

1 2 Previous Next