Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Define a Specific Date For Use in Calculation
 Login/Join
 
CB1125
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, 2016Report This Post
Tamra
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, 2009Report This Post
George Patton
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, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders