Do not include negative numbers in calculation 1

[newbie0423]

Hello,
I'm using CR XI. I am working on a report that shows the time a procedure is scheduled,(@bookedtime) and the time the patient went into the room (@Ptintime). In my report I am finding the difference between the two times. However, there are times when the Ptintime is less then the bookedtime. This results in negative numbers. I would like to sum the results of the two numbers. What happens when I try to do this is that it adds all of the numbers and then subtracts the negative numbers. This causes the total to be incorrect.

Example

6
-38
29
43
Total = 78
But the report is subtracting the 38.
I there a way to add the positive numbers without subtracting the negatives

 

[pmax9999]

There are a cople of ways you could do this.

Create a formula like this:

If	{@TimeDiff} > 0
Then 	{@TimeDiff}
Else	0

, where "{@TimeDiff}" is the formula that calculates the numbers in your example, and then Sum the result of the new formula to give you the total of the positive numbers.


Cheers
Pete

 

[newbie0423]

Thanks for responding Pete. However, when I use this formula it's not returning the correct sum.

 

[MCuthill]

newbie0423,

If I have understood your question correctly, I think the following should assist you. I am unsure why Pete's reply would not acheive what you are seeking though, as it should work (as best I can tell). Might be worth taking another look at his solution (namely that you sum his new formula, not the original).

Alternately, the following will only calculate a variance when it is greater than zero.

[blue]IF[/blue] {@Ptintime} < {@bookedtime} [blue]THEN[/blue] 0 [blue]ELSE[/blue] {@Ptintime} - {@bookedtime}

Again, this replies on the same principles as Pete's reply above (just in the first formula instead of creating a second formula). If this doesn't work, please provide an example scenario or two accompanying your reply; "it's not returning the correct sum" doesn't provide any information for others to try assist you in troubleshooting the issue (myself, and others are not sure at this time "why" Pete's solution didn't work and what it is returning in place of the expected result).

Hope this helps. Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[newbie0423]

Thanks for responding Mike. This is what I have for Pete's formula:
If {@BookedDiff} >0
then {@BookedDiff}
else 0
This returned 55.

BookedDiff formula is
DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@BookTime}),DateTime(CDate ("November 10, 1999"),{@PtInTime}) )

The numbers that I am calculating should equal 238. But it's coming up as 163 because
6
-38
29
0
43
-37
41
64
55

 

[MCuthill]

newbie0423,

I feel daft even asking... but you are summing Pete's new formula and acheiving those results, correct? If this is the case, I have something else to try... though I still must be missing something as to why Pete's solution isn't calculating properly. The alternative is a conditional running total, which I can assist in implementing should your answer to the above be "yes".

Apologies for the back and forth on this one, just has me a little stymied.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

Ugh... reread your post and see that you did get 55 (expecting 78). I will start cheffin up the conditional Running Total formulas for you.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

i am not sure, it is a really rough monday around me, but maybe something like the below.


//{@addifmorethanzero} //place in details
numbervar additup;

IF (DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@BookTime}),DateTime(CDate ("November 10, 1999"),{@PtInTime}))) < 0
then additup := additup
else additup := additup + DateDiff ("n",DateTime(CDate ("November 10, 1999"),{@BookTime}),DateTime(CDate ("November 10, 1999"),{@PtInTime}) )

//{@showtotal} //place in group footer
numbervar additup;
additup

//{@resettotal} //place in lower group footer or group header if you need the total to reset per group
numbervar additup := 0;

 

[MCuthill]

Thanks Fisheromacse, I got a call and ended up in a meeting since my last post.

newbie043: Fisheromacse's post is more or less what I would have provided. I posted something quasi-similar the other day, should you wish to take a look. It isn't exactly the same, of course, but the theory in the approach is the same.

Link: thread767-1712972

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[pmax9999]

As long as you are summing the formula I provided and not your {@BookedDiff} formula , I don't see any reason why the results are not as expected, and I do not see why it would be necessary to use variables.

I am assuming that the the two formulas referred to in your {@BookedDiff} formula ({@PtInTime} & {@BookTime}) return "times", and that the results you are attempting to sum are minutes. In which case, try simplifying the approach as follows:

If	{@PtInTime} < {@BookTime}
Then 	0
Else	({@PtInTime} - {@BookTime})/60

This is essentially what was suggested by MCuthill, with a slight adjustment to reflect the fact that the result of one time being added/subtracted to/from another is in seconds (ie, divide it by 60 to make it minutes),

Again, it will only work if you total this new formula, rather than your original {@BookedDiff} formula.

If this doesn't give you the results you are looking for, please post the details of the {@PtInTime} & {@BookTime} formulas (plus any other formulas referred to within them).

Cheers
Pete.

 

[newbie0423]

Hi Pete,

I got it to work!!! Thanks so very much!!!