Technical Questions: Subtraction formula

Print Page | Close Window

Subtraction formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7803
Printed Date: 04 May 2024 at 3:52am

Topic: Subtraction formula

Posted By: clumbsy
Subject: Subtraction formula
Date Posted: 25 Sep 2009 at 5:25am

I have a report grouped by account number , sorted by Datetime

my datetime populates individual read (electric meter reads)

example

9-5-09 6200

9-4-09 6000

9-3-09 5800

what I need to do is to get a daily use which would require

taking 9-5-09 6200 minus 9-4-09 6000 to get usage of 200 and so forth down the calendar for each account. Then just pull in any days that have a negative figure.

Replies:

Posted By: FrnhtGLI
Date Posted: 25 Sep 2009 at 6:11am

So is your report one that only shows negative usage?

You could do something like:

global numbervar nUsage:=0;

global numbervar nUsage:={usage}-next{usage};

nUsage;

or even just:

{usage}-next{usage}

You will have to be sure that {usage} is a currency or amount though.

This will work if you are trying to show usage starting with the first records. For instance, in your example, this would yeild 200 for date 9-5-09. If you wanted it the other way around change Next to Previous.

One problem you may have though is when the formula doesn't have a next or a previous record to calculate. Such instance would be true for the last record with Next and the first record with Previous. You could start with this and move on from there.

Another idea would be to use nextisnull and previousisnull. Something like:

global numbervar nUsage:=0;

global numbervar nUsage:={Usage}- (if nextisnull({Usage}) then

{Usage} else next({Usage}));

nUsage;

This should give you an amount of 0 if there is no next usage field (replace nextisnull with previousisnull if you are doing it the other way).

Once you have the usage calculated, suppress detail sections where nUsage>0.

Hope this helps or at least gives you some ideas.

Posted By: clumbsy
Date Posted: 25 Sep 2009 at 7:18am

Oh my, I have not even heard of global var. will look it up.

Thanks

I have three crystal books and can not find global var in any.

Posted By: FrnhtGLI
Date Posted: 25 Sep 2009 at 7:41am

Have you ever worked with local variables?

Essentially, a local variable is restricted to one formula and that value cannot be accessed from a different formula.

A global variable lets you access the value in any formula that declares the variable (except for sub reports, for that you would need a shared variable).

Variables are pretty useful. I suggest getting familiar with them.

Posted By: clumbsy
Date Posted: 25 Sep 2009 at 7:43am

No I haven't.

Thanks for the info.

Posted By: clumbsy
Date Posted: 25 Sep 2009 at 12:11pm

global numberVar nUsage :=0;
global numberVar nUsage :={BI_INTERVAL_RDGS.BI_RDG}-(if nextisnull({BI_INTERVAL_RDGS.BI_RDG})then{BI_INTERVAL_RDGS.BI_RDG}
else next ({BI_INTERVAL_RDGS.BI_RDG}));
nUsage;

This is what I have and is populating the correct information other than my oldest date is beginning its calculation with the read from next record.

Am I missing on how to start new on change of record?

Posted By: lockwelle
Date Posted: 25 Sep 2009 at 12:16pm

usually if the number is to be reused, you write a simple formula to reset it, like your first line:

global numbervar nUsage :=0;

""//hides the display of the 0 on the report.

then you place it, usually in the header of the group that you want to sum on. Every time the group changes, the variable will be reset to 0.

HTH

Posted By: FrnhtGLI
Date Posted: 25 Sep 2009 at 12:46pm

My impression was that the numbers were not to be reused in the report and that the formula was going on a detail line.

The first line of the formula:

global numbervar nUsage:=0

as lockwelle said resets the variable to 0. If this formula (the full formula from above) is placed on a detail line, it will:

1st - set the field to zero.

2nd - calculate the number (current usage -(current usage(if there is no

next record) or next usage(if there is a next record))).

3rd - display the calculated number.

This process will happen every time the detail line the formula is place on is evaluated, therefore should always start with zero then calculate and display the calculated number.

Posted By: clumbsy
Date Posted: 25 Sep 2009 at 12:53pm

It is in the detail section, sorry I must be miss understanding. Is global numbervar nUsage:=0 suppose to set it to zero after each change of group. I appologize I should have stated change of group not each record.

Posted By: FrnhtGLI
Date Posted: 25 Sep 2009 at 1:02pm

If you follow what lockwelle said it will.

You will have to take:

global numbervar nUsage:=0;

out of the original formula and create a new 'Initialize' formula with only that in it. Place the new formula in the group header and the 'Calculate' formula in the detail section.

If you have multiple groups, you will have to put the 'Initialize' formula in each group header.

Posted By: clumbsy
Date Posted: 28 Sep 2009 at 6:05am

This is my formula in my group header (which is group by account)

global numberVar nUsage :=0;

this is my formula in my details section

global numberVar nUsage :={BI_INTERVAL_RDGS.BI_RDG}-(if nextisnull({BI_INTERVAL_RDGS.BI_RDG})then{BI_INTERVAL_RDGS.BI_RDG}
else next ({BI_INTERVAL_RDGS.BI_RDG}));
nUsage;

Problem is my group is not setting back to zero it is subtracting the RDGS from the next record.

example:

Account Date RDG Usage

1001 9-24-2009 59927 9

9-23-2009 59918 10

9-22-2009 59908 8

9-21-2009 59900 54555 this is where it subtracts RDG

from next record

1002 9-24-2009 5345 1 It took my 59900-5345=54555

but odd thing is it shows like this for each account on my report but on the drill down of a particular account number it shows it reset to zero. I need to use the report and not the drill downs.

Can you help?

Posted By: FrnhtGLI
Date Posted: 28 Sep 2009 at 8:30am

So the Usage for 9-21-2009 should be 0?

Because it's the end of the group, there is still a next record, so it is using that number of the next record. Try something like:

global numberVar nUsage :={BI_INTERVAL_RDGS.BI_RDG}-(if nextisnull

({BI_INTERVAL_RDGS.BI_RDG}) or next({Account})<>{Account} then

{BI_INTERVAL_RDGS.BI_RDG}
else next ({BI_INTERVAL_RDGS.BI_RDG}));

This will give you an amount of 0 for the lowest date for an account number.

Hope this helps.

Posted By: clumbsy
Date Posted: 28 Sep 2009 at 9:18am

That works.

Thanks so much.

Print Page | Close Window