Running Total for latest record works most of the time

[jrhessey]

I've got a report that is pulling items purchased and unit price. I've been asked to only pull the latest price though and sum the qty purchased per part number. I've created a group for customer # then a group for item #. In the Item # footer I have qty purchased sum and the running total field.

The formula is set to reset on the change of each item number group and the running total formula is

(
onlastrecord or
{@date_1-2} <> next({@date_1-2})
) and
{so_history.OrderDate} = YearToDate

The date_1-2 field is formula field I made to figure out if the date was year to date or last year, year to date. It is

if {so_history.OrderDate} in YearToDate then 2 else 1


Here is a picture of what I'm getting, the selected field is my running total field.

http://www.crawfordmachineinc.com/runningtotalfield.jpg

I'm running CR 8.5.

Thanks for any ideas.

 

[jrhessey]

Nevermind guys, went a different way, and used a formula field instead of a running total field.

 

[jrhessey]

ok, I didn't get it completely right... The Previous year still isn't working. Current year is fine though.

 

[lbass]

I think I would use the following for the evaluation formula:

(
onlastrecord or
{table.partno} <> next({table.partno})
) and
{so_history.OrderDate} = YearToDate

-LB

 

[jrhessey]

First off thanks for your help LB, I hope I don't confuse you more with this post, but here goes...

I left out the fact the want two qty's purchased, one for this year to date, and one for last year to date. The same with unit price. They want the qty's summed and the latest unit price for each year to date. So in the detail section I have something like this...

Item # - 123456
SO#---SODate---QtyPurCYR---UnitPriceCyr---QtyPurPyr---UnitPricePyr
125 1/2/2005 25 1.25
127 1/9/2005 30 1.36
166 1/7/2006 60 1.21
168 1/9/2006 75 1.21
Totals 135 1.21 55 1.36


The formula for QtyPurCyr is

if sodate = yeartodate then soqtyordered

The formula for QtyPurPyr is

if {so_history.OrderDate} in
date(year(currentdate)-1,01,01) to dateserial(year(currentdate)-1,month(currentdate),day(currentdate))
then {so_history.RevisedOrderQuantity}

The Formula I did for LatestPriceCyr is

if {so_history.OrderDate} = Maximum
({so_history.OrderDate},{so_history.ItemNumber})
and {so_history.OrderDate} in yeartodate then
{so_history.LastUnitPrice}


The formula I have for LatestPricePyr is

if {@date_1-2}<>next({@date_1-2})
and {so_history.OrderDate} in
date(year(currentdate)-1,01,01) to
dateserial(year(currentdate)-1,month(currentdate),day(currentdate))
then {so_history.LastUnitPrice}


I'm pretty sure your post is correct LBass for information that doesn't change in the detail section like mine, because I need "2" last records in the detail section. Am I going in the right direction?

I've gotten most everything sorted out. Everything works except LatestPricePyr. If I place it in the group footer, it doesn't work, if I place it in the detail section it works perfectly. I'm assuming I have to put the group name in somewhere, but I only get errors when I add the grouped field name to the fields in the formula. What am I doing wrong?

 

[lbass]

I think I would use a formula like for past year {@pyrdate}:

if {so_history.OrderDate} in date(year(currentdate)-1,01,01) to dateserial(year(currentdate)-1,month(currentdate),day(currentdate)) then {so_history.OrderDate}

Then in the running total for past year, use an evaluation formula like:

{@pyrdate} = maximum({@pyrdate},{so_history.ItemNumber})

-LB

 

[jrhessey]

Damn that looks simple... Can't believe I didn't think of that. Man do I feel stupid right now...

Tried it, worked great, as usual, LB! Thanks for your help again!