Calculated key fields

[AndrewBruno]

I have a product file joined to a sales order file, with a key of product ID.

I want to show sales by quarter for the last 3 years. i.e. current quarter and the previous 11 quarters. I also want this to change automatically at the beginning of a new quarter, the data being rolling. I know that I need a calculated field in the sales order field to produce a key from the invoice date (which is in the sales order file), which can then be concatenated with the product ID , let's call them A to L.

I think I'm OK on setting up the relationships, but need some help on calculating these key field values.

Any help appreciated.

Anrew Bruno

[CobaltSky]

Hello Andrew,

Rather than working from dates - which requires complex range matching, I suggest that you use calcs which append quarter codes to the productID, by adding a quarter identifier to the current year (eg 20031 for the 1st quarter of 2003).

The calc (stored, result type text) in your order file would be along the lines of:

ProductID & "-" & Year(SaleDate) & (Int((Month(SaleDate) - 1) / 3) + 1)

Once that is in place, you will need a corresponding calc (unstored text) in your product file to produce a text multi-key result which will roll forward. For example:

ProductID & "-" & Year(Date(Month(Status(CurrentDate)), 1, Year(Status(CurrentDate)))) & (Int((Month(Date(Month(Status(CurrentDate)), 1, Year(Status(CurrentDate)))) - 1) / 3) + 1) & "