Jump to content
OneStop

Calculation on Empty Field

Recommended Posts

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? 

Share this post


Link to post
Share on other sites

Case(

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

)

Share this post


Link to post
Share on other sites

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 by OneStop

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by OneStop

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites

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

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

By using this site, you agree to our Terms of Use.