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
DEF 3.91 2/1/2012 3.96
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,
Windows, All Outputs
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.
WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
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
WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
|Powered by Social Strata|