How do I use a specific date (not contained in my table) in a calculation? For example:

Heres a sample of my report. I want to look in the EffectiveDate column to find the nearest date to a specific date (not contained in my table) of 1/1/2013 and return the nearest UnitPrice by ProductName.

My date to compare (not in table) is: 1/1/2013

ProductName UnitPrice EffectiveDate Jan2013Price
ABC 3.75 2/1/2012 3.90
3.90 10/2/2012
4.18 1/2/2013
4.30 7/2/2013

DEF 3.91 2/1/2012 3.96
3.96 10/20/2012
4.20 1/20/2013
4.25 7/25/2013



I would like to display the located UnitPrice in a new column titled Jan2013Price then grouped by product in my report:

Can this be done?

This message has been edited. Last edited by: Tamra,

CB1125,

Welcome to Focal Point!.

What you are trying to do can be handled with a calculated field and IF THEN ELSE logic. Depending on how you would like the final result sorted and if you would only want the data that meets your criteria you may also need a Fitler and a HOLD file.

You can search in the InfoAssist forum and find examples of the IF THEN ELSE logic.

In the text box for the calculated field
( found in the Calculation tab / Summary(compute)).

IF THEN ELSE would need a range of some sort if it is not an exact match for the date or possibly the use of LE or GE.


Check out the videos for InfoAssist.

Hope that helps you out.

What you will need to do is create a DEFINE that calculates the difference between your target date (January 1) and the actual sale dates. You can do this by simple subtraction if you are using smart dates.

Since some of your dates will be before the target and some afterwards you will need to use the ABS function to get a standard difference.

Then you use the record with the smallest difference as your January 1 price.

So something like this:

DEFINE FILE XYZ
DIFF/D7=ABS(TARGET_DATE - SALE_DATE);
END

TABLE FILE XYZ
SUM
COMPUTE MIN_DIFF/D7=MIN.DIFF; NOPRINT
PRINT PRICE SALE_DATE TARGET_DATE
BY PRODUCT
WHERE DIFF EQ MIN_DIFF
END