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     [SOLVED] Can I calculate a weighted average date field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Can I calculate a weighted average date field
 Login/Join
 
Carol Dobson
Guru
posted
I have the need to calculate a weighted average date field that's in a smart date on a report, and later divide it to come up with the weighted average date. Is that possible? Seems I can only do simple math with the dates, addition and subtraction. If all else fails, I know it can be done in Excel.

Thanks to anyone who may have any ideas. TGIF!

Carol

This message has been edited. Last edited by: Carol Dobson,



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
j.gross
Virtuoso
posted Hide Post
As in Excel, smart dates are basically ordinary numeric (integer) fields, representing offsets from an epoch date, with special rules applied at output-formatting time. (they also have special rules when casting directly between them and legacy I8MDYY-type dates)

So, if you average them (or perform any other manipulations that ultimately yield a valid offset value) you should get a valid date.

Given DATE/MDYY as the field you wish to average, this approach should do it:
DEFINE ...
  WEIGHT          /D1.6= ...;
  WEIGHTED_OFFSET /D12.6 = WEIGHT * DATE ;
END
TABLE ...
SUM
  WEIGHT 
  WEIGHTED_OFFSET
COMPUTE AVERAGE_OFFSET/I6= WEIGHTED_OFFSET / WEIGHT;
COMPUTE AVERAGE_DATE/MDYY = AVERAGE_OFFSET;
BY ...
END


Note that WEIGHTED_OFFSET, when summed, is not a proper offset value; but the division in the first compute brings it back down to Earth, and that's all we need.

If WEIGHT values are fractional, WEIGHTED_OFFSET should not be an integer field (to avoid imprecision in the summing process). The calculated ratio has to be converted to Integer (whether by rounding or truncation) at some point; I prefer to do it in the first compute, keeping the casting of offset to a date field as basic as possible. But it would probably work if you did it all togather:
COMPUTE AVERAGE_DATE/MDYY = WEIGHTED_OFFSET / WEIGHT;

This message has been edited. Last edited by: j.gross,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Carol Dobson
Guru
posted Hide Post
Thanks Jack, I'll give this a try. I think my issue was I was using very large numbers to do the weighting. I'll post my results.



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report This Post
Carol Dobson
Guru
posted Hide Post
I got it work Jack, thanks very much.

Have a good weekend!



WebFOCUS 7.6.6/TomCat/Win2k3
 
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003Report 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     [SOLVED] Can I calculate a weighted average date field

Copyright © 1996-2020 Information Builders