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,

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,

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.

I got it work Jack, thanks very much.

Have a good weekend!