Hi, I haven't read the entire thread just your first post so apologies if I'm treading on toes, but this is how I'd do it.
First create a blank formula, for example's sake we'll call it @group.
Now create another formula as follows, we'll call it @month.
@Month
left(monthname(month({table.datefield})),3) & "-" & year({table.datefield})
Now create 2 groups, the first being on @group and the second on @month.
Now create a formula as follows to find the most recent forecast within each month, we'll call it @Jan12.
if {table.datefield} = maximum({table.datefield},{@month})
then {table.amountfield}
else 0
Create a version of the above for each month, then summarize as follows.
@Jan12sum
(Instead of creating summaries you could sort the records descending by {table.datefield} and just use the Jan12 formula in the group 1 header/footer - I prefer using formulas though).
That should work, you could do this all automatically using the pivot function in a SQL command, however.
Regards,
Ryan.
EDIT: Minor amendments.
Edited by rkrowland - 05 Mar 2012 at 3:55am
IP Logged
