Basic Calculation Help

[Mark Appleby]

I am having a problem. I need to create a report of sales on Items. The total sales of an Item is sum(saleitemsQty). But how do I report the sum of sales this year, Last year, this quarter, Last quarter etc. the only way I can see to do it is to find and set field with sum of sales but this would be very very long winded. How should I go about this?

[falkaholic]

Summary fields work on the found set. So all you need to do is do a field on the range you want. i.e. "1/12002..3/31/2002" in a find (user Insert Calculated Resualt script step) then it should all add up.

HTH

[kennedy]

Read the manual/help on "subsummary" layout parts. If you define such a part that breaks on year, sort your report on year, and have that sum field in that subsummary part, you'll get what you want.

[Mark Appleby]

My problem is I want to present the data on up to 15000 stock lines in the following format:

Item ref: Stock qty: Sales q1: Sales q2: Sales ty: Sales LY:

With all results going accross the page.

[CobaltSky]

For this you will need to create several calculating fields based on relationships to the file with your sales line items in it.

First, in your sales file, create a stored calculating text field called ItemQuarter which draws on the existing item no and sale date fields, to create a composite key which identifies entries by item/quarter. The formula for this field would be along the lines of:

ItemID & "|" & (Int(Month(SaleDate) / 4) + 1))

Then you should create four unstored calculating text fields in your items file with the corresponding formulae:

ItemID & "|2"

ItemID & "|3"

ItemID & "|4"

ItemID & "|5"

Then create four relationships which link each of the above fields in turn to the ItemQuarter field in your sales file. call the relationships Q1summary, Q2summary, Q3summary and Q4summary.



Finally, create four unstored calculating fields with formulae along the lines of:

Sum(Q1summary::SaleAmount)

These fields will present up-to-the-minute read-outs of the total sales in each quarter for each item in your items file.



Note that as described above, results for a given quarter in all years will be summed. If you want to specify a year, you should include year in the formulae. The ItemQuarter formula would then be along the lines of:

ItemID & "|" & Year(SaleDate) & "|" & (Int(Month(SaleDate) / 4) + 1))

You might then consider creating a global field where you specify the year you want data for, and redefine the key fields for the Q#summary relationships to use the formula:

ItemID & "|" & gReportYear & "|3"

You will then be able to get a report on sales by quarter for any year for which there are sales in your database. I haven't talked about the method for getting stock quantity, however a comparable technique using a calculating field with a Sum function, based on an appropriate relationship (eg to inventory records) should suffice for that also.

[Mark Appleby]

This is going to take a little while I will come back to you and let you know the result. I am currently having problems with the year function.