abs function not adding up - turning negative to positive

  • Thread starter Thread starter michael cu
  • Start date Start date
M

michael cu

Hi. I have a function that adds invoice values for date
ranges like so:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

The problem is that it's not treating my negative
DollarsShipped as negatives, so my MTDDollarsShipped value
is higher than it should be. Any thoughts on how to
restructure this would be fantastic. Thanks!
 
michael cu said:
Hi. I have a function that adds invoice values for date
ranges like so:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

The problem is that it's not treating my negative
DollarsShipped as negatives, so my MTDDollarsShipped value
is higher than it should be. Any thoughts on how to
restructure this would be fantastic. Thanks!
Click to expand...

Turning negatives into positives is what the Abs() function is intended to
do. If that's not what you want then why are you using it?
 
You're right. I just figured that out a few minutes ago.
It was working until we had to issue credits. How silly of
me. I looked back to another thread and found that someone
suggested the following, but it doesn't seem to work:

MTDDollarsShipped: -Sum((([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth])*[DollarsShipped]))

Any thoughts?

-----Original Message-----
Hi. I have a function that adds invoice values for date
ranges like so:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

The problem is that it's not treating my negative
DollarsShipped as negatives, so my MTDDollarsShipped value
is higher than it should be. Any thoughts on how to
restructure this would be fantastic. Thanks!
Click to expand...

Turning negatives into positives is what the Abs() function is intended to
do. If that's not what you want then why are you using it?


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
When I first saw the thread that had the abs function in
it, I though abs was an abbreviation for abstract. That's
why I used it until I found out it was absolute value :)
-----Original Message-----
Hi. I have a function that adds invoice values for date
ranges like so:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

The problem is that it's not treating my negative
DollarsShipped as negatives, so my MTDDollarsShipped value
is higher than it should be. Any thoughts on how to
restructure this would be fantastic. Thanks!
Click to expand...

Turning negatives into positives is what the Abs() function is intended to
do. If that's not what you want then why are you using it?


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


.
Click to expand...
 
Try moving the abs or use an IIF statement.

MTDDollarsShipped:
Sum(Abs([InvoiceDate]>=[Forms]![Dashboard]![FirstDayOfMonth])*[DollarsShipped])

OR

SUM([InvoiceDate]>=[Forms]![Dashboard]![FirstDayOfMonth],[DollarsShipped],0)
 
Nope. Thanks for trying though. The first one turned my
negatives positive as well and the second one didn't have
the right number of arguments. What would you suggest with
an IIF statement? I tried using summing separate
equations; one for the positive DollarsShipped values and
one for the negatives, but no luck.

-----Original Message-----
Try moving the abs or use an IIF statement.

MTDDollarsShipped:
Sum(Abs([InvoiceDate]>=[Forms]![Dashboard]! [FirstDayOfMonth])*[DollarsShipped])
OR

SUM([InvoiceDate]>=[Forms]![Dashboard]![FirstDayOfMonth],
Click to expand...
[DollarsShipped],0)

michael said:
Hi. I have a function that adds invoice values for date
ranges like so:

MTDDollarsShipped: Sum(Abs(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

The problem is that it's not treating my negative
DollarsShipped as negatives, so my MTDDollarsShipped value
is higher than it should be. Any thoughts on how to
restructure this would be fantastic. Thanks!
Click to expand...
.
Click to expand...
 
Michael

Let's back up a little. Could you describe, without using functions and
code, what you have as underlying data, and what you are trying to
accomplish?

More info, please...

Jeff Boyce
<Access MVP>
 
Ugh. I think it was working okay. I'm sorry to have caused
any confusion. I was comparing my numbers against Cognos
PowerPlay and, after drilling down further, it looks like
PowerPlay is wrong and my numbers are right. I was looking
at our company's sales at the territory level. When I
started looking at the account level, I started seeing
some negatives. Should be all set. Thanks.
 
Back
Top