conditional sum()?!

[bobydick]

hi all...

i have a field that has positive and negative dollar amounts in it. i was wondering if its possible to do a sum on only the positive ones and then only the negative ones. separately.... is it possible?

thanks...

[David Jondreau]

There's several ways to do this. The simplest I think would be to create two new fields, both calculations. The first would show the total if it's positive, the other if it were negative, then sum those.

For positve values try, =Max(total; 0) and for negative = Min(total;0).

[comment]

If separate summaries are required, why is the data in the same field to begin with?

[LaRetta]

You haven't said what this is for. I envision something like charges and payments being in same dollars field; or quantity purchases and sales being in same field, as plus (number alone) and minus numbers.

And now you want to know what are charges total and what are payments total? Or what are purchases quantity and what are sales quantity? Do you not have a TYPE field to say whether it is charge or payment or whether purchase or sale? You might want a conditional relationship which filters by Type. Just another approach to consider here ...

[comment]

I envision something like charges and payments being in same dollars field

That's a good point - and I would add that if this is the case, then a Type field is really a must, since both types can be either positive or negative amounts (e.g. a charge being canceled).

[LaRetta]

since both types can be either positive or negative amounts

Indeed. And many people get this wrong; scratching their heads on how to create an adjustment. They end up creating another Type field and then having to hard-code add them together. Yuk.

UPDATE: I got side-tracked on the yuk ... point being that I don't think we have all the information here and that a Type probably DOES exist and we should use that to filter the groups.

Edited July 18, 2008 by Guest
Added update

[comment]

I don't think we have all the information here and that a Type probably DOES exist and we should use that to filter the groups.

Indeed - and if this is a report, then sub-summarizing by Type is all that's required. :P

[LaRetta]

Not necessarily. There are many times that these totals should be available in browse mode, no? :wink2:

[comment]

LOL.

[LaRetta]

For those that may not realize ... Michael and I had the same conversation not too long ago except I took the other side (the report side) in that one. Ha ha. :king:

[Søren Dyhr]

Indeed - and if this is a report, then sub-summarizing by Type is all that's required.

But type could be the sign, although not being so normalized as it could be admitted, I have occational found myself subscripting to this approach:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

...where it translated to this might be done this way:

Case(Choose(Sign(Extend ( aField ))+1;2;"";1)=Get ( CalculationRepetitionNumber );Abs( Extend ( aField ) ))

--sd

[comment]

I'm afraid you're reading this out of its context, which was:

if this is the case, then a Type field is really a must, since both types can be either positive or negative amounts

[Søren Dyhr]

No becasue what you and LaRetta are on about, is quite close to a hijacked thread ... where in the original post is there any thing about, what you're turning the thread into?

i have a field that has positive and negative dollar amounts in it. i was wondering if its possible to do a sum on only the positive ones and then only the negative ones. separately.... is it possible?

--sd

[LaRetta]

Let me get this straight, Soren, YOU are accusing US of hijacking this thread? Scroll back up and check it more closely. Then go get real and come back.

[Søren Dyhr]

Am I somehow excluded from the following post from bobydick, I can only spot one post at all, and here is my answer as good as anybody elses, is he x-posting the purpose and context in another thread - then is he/she certainly to blame, not the ones I supposed were hijacking ... and I better issue an appology straigh away!

--sd

[LaRetta]

Now I have no idea what you just said, Soren, but it made me laugh (in a good way). I've had little sleep and maybe that's why. So I'm off to the lake with my dawg, Ginger and you and Michael can sort it out for us. Maybe even bobydick will come back and help. But the fact that Comment and I were discussing what MIGHT be wanted here isn't known as a highjack. :wink2:

Edited July 18, 2008 by Guest

[Søren Dyhr]

When is jumping into cruisecontrol natter following you own agenda, not highjacking? ... I'm not thinking english so putting the right term on the going ons, might be my fault. Hijacking is in my language, taking a vessel out of service to make it obtain different purposes than the legitimate owners intentions. Give me some other synonymous words for those things gradually in between?

--sd

[LaRetta]

This has nothing to do with OUR agenda, Soren. We were trying to read between the lines on what was needed here to help someone. Even YOU do that; we all do. Good Developers sometimes need to see what isn't being said just as much as what is. GET OFF OUR CASE. You are in one of your moods to just pick fights (you've been jumping into other threads with the same energy) and I will NOT be a part of it. :thumbdown: