We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Using previous group sum in current group calculation

nandand
nandand asked
on
Medium Priority
566 Views
Last Modified: 2010-07-23
SQL Server and CR 8.0 and ODBC

I'm creating a summary executive report for accounts receivable trends.  The report is grouped by month (using a date/time field) with the sums based on formulas that are in the detail section (which is hidden).

Report currently looks like this:

Month  Sales  Cash  Balance
Jan     XXX    XXX    XXX
Feb      etc...
Mar      etc...

I would like to add a column for Sales Two Months Prior and then be able to perform a calculation on that field.  So the March line would include the January sales sum.  Is there any hope?

Comment
Watch Question

Commented:
This formula will produce the value two prior - substitue your own field name of course.

shared numberVar TwoPrior;
shared numberVar OnePrior;
whileprintingrecords;
if RecordNumber = 2 then
(
    OnePrior := Previous({TABLE.NUMBERFIELD});
);
if RecordNumber > 2 then
(
    TwoPrior := OnePrior;
    OnePrior := Previous({TABLE.NUMBERFIELD});
);

TwoPrior;
nandandconsultant

Author

Commented:
I could not get the previous function to work.  However I do have the solution which is a 4 formula method.  
In Group Header section:
whileprintingrecords;
numbervar CurMonth := 0;

In Detail section:
whileprintingrecords;
numbervar CurMonth := CurMonth + {table.sales});

Create a second group footer section.  In the first group footer:
whileprintingrecords;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder(month(table.datefield},2) = 0 then
LastEvenMonths
else
LastOddMonths

In the second group footer section:
whileprintingrecords;
numbervar CurMonth;
numbervar LastOddMonths;
numbervar LastEvenMonths;
If remainder (month(table.datefield},2) = 0 then
LastEvenMonths := CurMonth
else
LastOddMonths := CurMonth

I do not take credit for this. I got the advice from a different tech help site.  This did exactly what I needed it to do.
Thanks for your help, though.
nandandconsultant

Author

Commented:
Before I close this question I'd like to clarify and ask a question - I needed to grab a group sum that was two months old to use in the current group sum.  I could not get Previous to work with the group totals.  If I'm incorrect in my assumption, please clarify.  Otherwise I will close question and ask for a refund.  

P.S. I'm very grateful I got a response.  Thank you for your time.

Commented:
Hey nandand,

You are correct that previous isn't going to work.  

Just curious though what didn't work about my formula?  Did you put it in the group footer?  I'm just asking because a) I've used it several times before and it works fine for me and b) sounds like the answer to your latest question.

In any event, I'm glad you have it working.

frodoman
nandandconsultant

Author

Commented:
frodoman,

I'd love to get your solution to work if for no other reason than I will learn from it. If I can figure it out - I'll give you the points.

Question: I'm confused as to what you mean by the Previous({TABLE.NUMBERFIELD}); ....Since this formula is in the group footer what field should I use?  My sales field?  That simply yields the last detail record when I need the sum amount.  If I use the group sum, I get an error that says there is no previous data.  Same for a running total.  Is there another formula not stated?

Next Question: The remainder also gives me the last record's record number which is a very high number since I have lots of data.  So what does the 2 get me?

I'm sorry for being so slow.  I really would like to understand your concept if you have the time.  If I need to explain my problem better please let me know.  Thank you!
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
In answer to your "remainder" question, the remainder function gives you just that:

Remainder(5,2) = 1   ( 5 divided by 2 leaves a remainder of 1 )
Remainder(72,2) = 0  (72 divided by 2 leaves a remainder of 0 )

The remainder function is commonly used to get even multiples.  For example to do something every 14th record you would do a formula:  If Remainder(RecordNumber,14) = 0 Then...

Hope that makes sense.
nandandconsultant

Author

Commented:
I haven't tried your formulas (too many reports to write!) but how about using groupnumber instead of recordnumber.  Does that make sense or am I off base?

Commented:
That makes sense to me.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.