I have two fields called MAP and Price. I have another field called Profit which is a calculation of (MAP - Price). However for some records the MAP isn't required and is therefore empty or has 0. So of course profit ends up being calculated as a negative in those instances. Is there a better way to make that calculation so that it ignores the empty or 0 MAP field? 

Case(

   isempty (Table::Map); Table::Price;Table::Map-Table::Price

)

that didn't seem to work. The Profit field is still just showing as a negative. Most of the MAP fields actually have a value of 0 in them.

Edited January 9, 20179 yr by OneStop

What type of field is MAP? And what should the result be when MAP is 0 or empty?

MAP is a number field.

 

The problem is thus: I have a Price field and MSRP field, a MAP field, a Profit from MSRP and a Profit from MAP field.

Price of course is the vendor cost. To calculate basic profit I just subtract Price from MSRP (MSRP - Price) = Profit from MSRP

But in some cases there is a MAP price which I use to calculate the MAP profit. (MAP - Price) = Profit from MAP 

A lot of the records don't actually have a MAP price so the field is set to 0. In those cases when I calculate the MAP - Price, it's showing a negative value. 

You did not answer my question. What should the result be when MAP is 0 or empty?

Ideally, I'd like it to not show anything. In other I'd like the Profit from MAP to only be calculated if the value in MAP is >0

Edited January 9, 20179 yr by OneStop

18 minutes ago, OneStop said:

I'd like the Profit from MAP to only be calculated if the value in MAP is >0

Well than either:

If ( MAP > 0 ; MAP - Price )

or just:

If ( MAP ; MAP - Price )

should work for you.

 

Although knowing a little about what MSRP and MAP stand for, I would expect you to actually want  to do:

If ( MAP ; MAP ; MSRP )  - Price 

that is subtract the Price from MAP, if it exists - otherwise subtract it from MSRP.

 

--
Note: Ideally, if you don't have data for a field, the field should be empty - not contain 0.

The 0 got imported from the Vendor's CSV file that way. I suppose I could have replaced it but I figure it's going to be an ongoing concern every time they import a new vendor file.

 

That last formula works perfectly. Thank You!

Quick question can you tell me what  the (MAP ; MAP ;  MSRP ) part actually does?

If ( MAP ; MAP ; MSRP )  - Price

16 minutes ago, OneStop said:

can you tell me what  the (MAP ; MAP ;  MSRP ) part actually does?

The If() function has the following signature:

If ( test ; result_if_true; result_if_false )

The test argument is always evaluated as Boolean, so that:

If ( MAP ; MAP ; MSRP )

is just a shorthand for :

If ( GetAsBoolean ( MAP ) ; MAP ; MSRP )

And the expression GetAsBoolean ( number ) will return false when number is 0 or empty. Since MAP is not expected to be negative, you could just as well use:

If ( MAP > 0 ; MAP ; MSRP )

 

 

Edited January 9, 20179 yr by comment

Excellent. Thank you so much for all your help. Learned something new today.