Subtract the first and last numbers by ID numbers

[pleashelp]

Using CR 8.5. I have a report that has 3 ID's for each meter. I am grouping on the meter and listing the 3 id's and each amount in the detail section. That's the easy part! Now I want to subtract the amount of the highest ID number from the amount of the lowest ID number. I tried to use maximum and minimum and couldn't get it to work. Anyone have any ideas?

Thanks

 

[Thadeus]

pleashelp,
Can you please include a some sample data layed out as it would appear on the page and the text of the formula that you tried. It will be helpful to getting your problem solved.

something like:
Group 1 Meter
{ID} {Value}
{ID} {Value}
{ID} {Value}
with the actual (or bogus) IDs and value's not the words 'ID' and 'value'

 

[mbarron]

Create the following formula and place it in your group footer wher {number.field} is the field you are calculating on, and {group.field} is the field you are grouping on:


Sum ({number.field}, {group.field})-maximum ({number.field}, {group.field})-minimum({number.field}, {group.field})

Mike

 

[pleashelp]

In response to mbarron...here is the formula that i created...which is wrong...

Sum ({@EFT In}, {EFTDayMeters.ID})-maximum (Sum ({@EFT In}, {EFTDayMeters.ID})
-minimum (Sum ({@EFT In}, {EFTDayMeters.ID})))

where @EFT In is the formula for the amount

 

[pleashelp]

in response to thaddeus...

It looks something like this..

ID EFT In

322 100.00
345 5.00
378 500.00

What i want to do is subtract the 500.00 from ID #378 (Maximum ID) from the 100.00 from the ID #322( the minimum ID)

 

[mbarron]

How is it wrong (what error are you getting?)

You have too many &quot"s at the end of what you posted.

Mike

 

[pleashelp]

first it tells me that a &quot" is missing, so i added another paren.. then it says that the summary/running total field could not be created

 

[mbarron]

Okay...

Is there always only 3 id's per meter?
Can you sort by id?

If the answer to both questions is yes then use the following formula to extract the value associated with the middle ID

whileprintingrecords;
stringvar out;
if {cf.meter}=previous({cf.meter}) and {cf.meter} = next({cf.meter})
then out:={cf.val} else
out:=out

insert the formula in the details band and suyppress the formula.


To display in a footer use the following formula:

whileprintingrecords;
numbervar out;


Mike

 

[mbarron]

I forgot the "wheres"

For the formula:

if {cf.meter}=previous({cf.meter}) and {cf.meter} = next({cf.meter})
then out:={cf.val}


cf.meter would be {EFTDayMeters.ID}, and cf.val would be {@EFT In}


Mike

 

[pleashelp]

Thanks Mike... but I don't think that is exactly what I want. yes there are always 3 id's in each group. I always want the difference between the amount of the lowest ID number and the amount of the highest ID number. So in the example above, I want it to show $400.00. But, I get a blank field in every group footer.

 

[lbass]

How do you identify individual meters--is there a higher order ID for the meter and then "sub-IDs" within each meter?
How about grouping on meterID and then sorting by subID and then creating a formula {@amtdiff}:

if {meter.subID} <> maximum({meter.subId},{meter.ID}) and{meter.subID}<> minimum({meter.subID},{meter.ID}) then next({meter.amt})-previous({meter.amt})

This will only display next to the middle detail record, so then use a variable to bring it into the group footer:

Place in group header:

whileprintingrecords;
numbervar diff := 0;

Place in details section:
whileprintingrecords;
numbervar diff := diff + {@amtdiff};

Place in group footer:
whileprintingrecords;
numbervar diff;

-LB

 

[pleashelp]

OK...I have managed to get the amount that goes with the maximum ID # and put it in the group footer. I got the amount that goes with the minimum ID # and put it in the group header. So...one amount for the two ID's that I'm interested in. Now I need to subtract that minimum amount in the header from the maximum amount in the footer. Any further ideas?

 

[synapsevampire]

Assuming that you've grouped by Meter, perhaps you can place the field in the details, right click it and select Insert Summary->Minimum and then do it again for Maximum.

Now create a formula, note that you'll have these minimum and maximum fields available to the formula, so just subtract them.

Now delete the minimum and maximum from the group footer and place your formula in the group header or group footer, whichever you need.

-k

 

[mbarron]

I was suffering from the dreaded &quot;not fully reading/comprehending the request&quot; with my last post.

If you are able to display the min and max ID's values, all you need to do is create a formula that subtracts the MIN formula from the MAX formula in the footer:

As simle as:
{@max.formula} - {@min.formula}


or

Using the &quot;sort by ID&quot; method:

Create the following and inserted into details band (suppressed):

whileprintingrecords;
numbervar mx;
numbervar mn;

if onfirstrecord or {group.field}<> previous ({group.field}) then mn:={value.field};
if onlastrecord or {group.field}<> next({group.field}) then mx:={value.field};

Then to display the results, place the following in the footer:

whileprintingrecords;
numbervar mn;
numbervar mx;
mx-mn






Mike

 

[pleashelp]

OK. I appreciate your help. Here is what I have so far. For the formula in the details section:

whileprintingrecords;
numbervar mx;
numbervar mn;

if onfirstrecord or Minimum ({EFTDayMeters.ID}, {@Slotid})<> previous (({EFTDayMeters.ID}))
then mn:={@EFT In};
if onlastrecord or Maximum ({EFTDayMeters.ID}, {@Slotid})<> next( ({EFTDayMeters.ID})) then mx:={@EFT In}

If i don't suppress the details section, I can see that the correct numbers are displayed.

For the Footer section, I have:


whileprintingrecords;
numbervar mn;
numbervar mx;
mx-mn

when i display the footers, all I see are zeroes--0.00

This is NOT what is supposed to deisplay. What am I doing wrong?

 

[mbarron]

This is not the same as I posted:

if onfirstrecord or Minimum ({EFTDayMeters.ID}, {@Slotid})<> previous (({EFTDayMeters.ID}))
then mn:={@EFT In};
if onlastrecord or Maximum ({EFTDayMeters.ID}, {@Slotid})<> next( ({EFTDayMeters.ID})) then mx:={@EFT In}


You need:
if onfirstrecord or {@Slotid}<> previous (@Slotid)
then mn:={@EFT In};
if onlastrecord or (@Slotid)<> next({@Slotid ) then mx:={@EFT In}


Mike

 

[pleashelp]

THANK YOU!!!! That did it!

 

[pleashelp]

Ok..I'm back. That worked great! Now I have this group footer subtotal, that we just created. I need to get a grand total of that field. I thought maybe a running total, but that field is not available to use in the running total. Once again, any ideas?

Thanks so much

 

[mbarron]

For the footer (suppressed):

whileprintingrecords;
numbervar gtotal;
gtotal:=gtotal + {@the field you just created}


For the Report footer;
whileprintingrecords;
numbervar gtotal



Mike

 

[pleashelp]

That worked great! Thanks again.