Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Define a Specific Date For Use in Calculation
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Define a Specific Date For Use in Calculation
 Login/Join
 
Member
posted
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,


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: November 09, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
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.


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Define a Specific Date For Use in Calculation

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.