Difference between first and last record in a group

[arodri]

Hello,

I've seen a couple other posts similar to what I need but still haven't been able to figure this out. Any help would be greatly appreciated!

I get milage records (listed below) from a View I created in SQL.
They are ordered by Code and Date. I need to get the difference between the last and first milage record for each Code (not min and max, but first and last according to the date).
For example, for code 084C it would be (26,832-3,594)= 23,238.
I also need to add the condition that if there's only one record for a code (i.e. code: 01BZ), then the difference is just 0.

I will then need to sum all of the 'difference' values.

Code Date Item Milage
01BZ 1/6/2011 FS Lube 63,251

01KW 4/26/2010 FS Lube 72,883
01KW 10/19/2010 FS Lube 82,226

084C 2/13/2010 Synthetic Lube 3,594
084C 6/7/2010 Synthetic Lube 11,014
084C 9/24/2010 Synthetic Lube 20,669
084C 12/21/2010 Synthetic Lube 26,832

15JY 2/17/2010 FS Lube 209,198
15JY 4/9/2010 FS Lube 212,578
15JY 6/2/2010 FS Lube 216,148
15JY 8/12/2010 FS Lube 22,688
15JY 9/25/2010 FS Lube 224,229
15JY 10/27/2010 FS Lube 228,162

Thanks again for any suggestions!

 

[briangriffin]

It can be done, but I'm wondering why with automobile mileage it would not be the min/max for the group? Won't the first record by date always be the min, and the last record the max?

 

[arodri]

Yes, I thought the same thing. I'm not sure of the exact reason but this is the way we need it to be done. I'm guessing one reason could be because of user errors. If they type in the wrong milage then it throws out the entire calculation. (look at the following record in my example:
15JY 8/12/2010 FS Lube 22,688 )


Any ideas on how this could be done?

 

[briangriffin]

I'd use an array - put this in the details:

whileprintingrecords;
numbervar array v_mileage;
redim preserve v_mileage[ubound(v_mileage)+1];
v_mileage[ubound(v_mileage)] := {MileageField};

The calculated total in the group footer:

whileprintingrecords;
numbervar array v_mileage;
v_mileage[ubound(v_mileage)] - v_mileage[2]


And reset the variable in the group header:

whileprintingrecords;
numbervar array v_mileage;
redim v_mileage[1];
0

Suppress the GH and detail formulas.

 

[arodri]

Thanks for your post!

I did what you suggested but I'm not getting the right answer. I have one group in my report - Code. When I put the calculated total in the group footer, here is what I get:

01BZ 0
01KW 9,343
084C -7,420
15JY 6,950

The sum of all of the differences for this group of data should be 51,545.

Am I doing something wrong?

 

[briangriffin]

Sorry, I didn't notice that you want to get the total of all the differences. But first we need to get those differences working.

My test report formula shows the group values as:

0
9343
22,238
18,964

Those look to be correct - are you sure you have the values sorted as per your example? The fact that some are correct, and that you have a negative value, suggests that they aren't in the same order in the report as they are in your post/my test.

If you click the A-Z icon you should see two items in the list - the first will be the group, which doesn't matter here, and next you should see the date field. If you click the date field you'll have the option of sorting ascending or descending, but in this case we need it to be descending.

 

[briangriffin]

Err, I mean ascending...

 

[arodri]

Hello,

Ok, I sorted the date field descending and that fixed the problem. I have all of the correct numbers now but they're all negative - I've never worked with arrays but I assume I just change the order of the substraction from the calculated total?

Now that I have the correct data, how would I get the sum of it all?

thank you!

 

[arodri]

Never mind on the negatives...it was the sort order. All of the totals are not correct.

 

[arodri]

Wow I can't type...so sorry. I meant to say all of the totals are NOW correct. Now it's just the sum..

 

[briangriffin]

Great, now your group footer formula becomes:

whileprintingrecords;
numbervar array v_mileage;
numbervar v_total;
v_total := v_total + (v_mileage[ubound(v_mileage)] - v_mileage[2]);
v_mileage[ubound(v_mileage)] - v_mileage[2]


and in your report footer place a new formula:

whileprintingrecords;
numbervar v_total;
v_total

Now I'm off to shovel more snow...

 

[arodri]

I'm not at work but I will try that Monday - Thank you soooo much for all of your help.

Good luck with all that snow!

 

[arodri]

Hello,

Thank you so much for all of your help - the formula worked perfectly!
If it's not too much trouble, would you mind explaining what this part of the footer formula does:

v_total := v_total + (v_mileage[ubound(v_mileage)] - v_mileage[2]);v_mileage[ubound(v_mileage)] - v_mileage[2]

Thank you!

 

[briangriffin]

Sure -

At the detail level we're populating an array of all the mileage figures for the particular code (the reset formula in the group header starts the array over again). So if we were to look inside the array at the group footer for code 084C, it would be:

[null,3594,11014,20669,26832]

The first value is null because I was too lazy to code around it.

Ubound(arrayname) gives the position of the last item in the array, so:

v_mileage[ubound(v_mileage)] is saying give me the value of the last item, which is 26832. We know the first actual value in the array is position 2, which is v_mileage[2].

So... v_mileage[ubound(v_mileage)] - v_mileage[2] equates to

26832 - 3594, which is 23238.

We can't just sum those figures, so we need another variable to accumlate them for the total.

v_total := v_total + v_mileage[ubound(v_mileage)] - v_mileage[2] is a running total that takes each subtotal and adds it to the previous subtotals.

Then in the report footer we just need to display that variable, which is the total of all the subtotals.

 

[arodri]

Ohh....that helps a lot! It all makes sense now.

Thanks again

 

[arodri]

One more question - this report is working fine now and the calculations are spot on but there are a lot more user errors than we originally thought.
Since it's taking the difference of the last record and the first records, I'd like to limit the results to only the records with differences of more than 0 or less than 50,000.

For example, right now (because of user error) I have a code with a difference of: -18,087. The user typed in the wrong values for the later date, so we get a negative. I also have the same thing happening but with numbers that are too big (in the millions) and this is throwing off my average (After I got the total for everything, I did an average).

Is there a way to eliminate these outliers and just leave the values for differences between 0 and 50,000?

Thanks again

 

[briangriffin]

This will be a little hard to explain, but I don’t have access to any online storage to provide a sample report. Basically you’re going to use a main report to determine which codes are valid, then pass those codes to a subreport to filter appropriately. So…

1. Copy the existing report. We’ll use it later as the subreport.

2. Put the following formula in the report header of the existing report:

whileprintingrecords;
stringvar array v_CodesToDisplay;
redim  v_CodesToDisplay[distinctcount ({CodeField})];
numbervar i := 0;

3. The formula in the detail section won’t change, but the formula in the group footer becomes:

whileprintingrecords;
numbervar array v_mileage;
numbervar v_total;

stringVar array v_CodesToDisplay;
numberVar i;
Local numberVar v_CodeGroupMileage;

v_total := v_total + (v_mileage[ubound(v_mileage)] - v_mileage[2]);
v_CodeGroupMileage := v_mileage[ubound(v_mileage)] - v_mileage[2];

If v_CodeGroupMileage in 0 to 50000 Then
    (
        i := i + 1;
        v_CodesToDisplay[i] := {Sheet1_.Code};
    )
;
v_CodeGroupMileage;

Notice we’ve hardcoded the range of acceptable values (0 to 50,000)

4. Insert an additional report footer, and put this new formula in report footer A (call this formula CodesToDisplay):

whileprintingrecords;
stringvar array v_CodesToDisplay;
numberVar i;

redim preserve  v_CodesToDisplay[i];
join (v_CodesToDisplay, ",");

5. Now if your refresh the report you should see a comma separated string of all the valid codes in the footer.

6. Insert the copied version of the report as a subreport in report footer B.

7. In the subreport, create a string parameter called Codes To Display.

8. Link the subreport to the main report using:

CodesToDisplay formula in main report to Codes to Display parameter in subreport.

8. Refresh the report. You should see all the codes in the main report, and only the valid codes in the subreport. If so, suppress all the sections in the main report and you should be good to go.

 

[arodri]

I got to step 5 in your instructions and I'm getting the error "An arrays dimenions must be an integer between 1 and 1000"

Are there too many cardnums for that to me the dimenions of the array? Is there a way around this?

Thanks again.

 

[arodri]

I ran a quick query in sql to find out what the distinct count for cardnums for this view (my source of the crystal report) and it's 366,848.

 

[briangriffin]

In the group footer formula change the threshold line to:

If not(v_CodeGroupMileage in 0 to 50000) Then


In the group selection formula of the subreport change to:

not(Trim({YourCodeField}) in Split ({?Codes To Display},"," ))


Now we're just passing in the codes that we want to exclude. This will blow up if you have no codes that are outside the threshold, but that doesn't seem to be a problem for you (as long as it's less than 1000).