Force Answer when = ?

[hutchlad]

Hey Guys,

Thanks for having me I'm a beginner please take it easy haha!

Basically I have a calculation field, where I am trying to force the answer as "0" when the calc returns "?" (due to an empty field etc, for whatever reason I'm not concerned about)

My calc is:

Round ( If ( IsEmpty ( Cash Sales Total Sales Value )=1 ; 0; (Cash Sales Total Last 7 Days-(Landed Cost*Cash Sales Total Last 7 Days)*1.2) / Cash Sales Total Sales Value ); 2)

Now it returns an answer when there is one, or a "?" when insufficient data, all I want is to return a "0" in this case rather than a "?"

 

Thanks in advance and sorry for the noob question

Edited July 27, 2017 by hutchlad
tags

[siroos12]

Maybe this?

if(Round ( If ( IsEmpty ( Cash Sales Total Sales Value )=1 ; 0; (Cash Sales Total Last 7 Days-(Landed Cost*Cash Sales Total Last 7 Days)*1.2) / Cash Sales Total Sales Value ); 2) = "?";

0

;Round ( If ( IsEmpty ( Cash Sales Total Sales Value )=1 ; 0; (Cash Sales Total Last 7 Days-(Landed Cost*Cash Sales Total Last 7 Days)*1.2) / Cash Sales Total Sales Value ); 2))

more professional method is using Let function:

Let(

Calculation = Round ( If ( IsEmpty ( Cash Sales Total Sales Value )=1 ; 0; (Cash Sales Total Last 7 Days-(Landed Cost*Cash Sales Total Last 7 Days)*1.2) / Cash Sales Total Sales Value ); 2);

if(Calculation = "?";0;Calculation)

)

 

[hutchlad]

Hi Siroos,

Brilliant! Thanks so much for helping me out, I really like the look of the Let function you wrote, however when use the calc, it does change the ? to 0 but also the true figures are different values than originally 

[siroos12]

My pleasure, is your calculation filed set to return numbers or text?

[comment]

3 hours ago, hutchlad said:

Basically I have a calculation field, where I am trying to force the answer as "0" when the calc returns "?" (

It would be helpful to see the original calculation, before you tried to "force" it. What you gave us is difficult to read, esp. when we don't know what the calculation's purpose is. I think you want do:

If ( 
Cash Sales Total Sales Value ; 
Round ( ( Cash Sales Total Last 7 Days - Landed Cost * Cash Sales Total Last 7 Days * 1.2 ) / Cash Sales Total Sales Value ; 2 ) ;
0
)

This tests that your divisor is not empty or zero and if so, performs the division and rounds the result. Otherwise it returns 0. Note that 0 does not require rounding.

--
P.S. IMHO, outputting an artificial zero result is bad practice. The correct result in case of division by zero should be either an error or nothing.