Post

4 followers Follow
0
Avatar

IF Calculation

Hello,

I was hoping somebody could please help me.

I am trying to create an IF formula to calculate a tax rate so for example if my drop down list has various percentages from Zero Rated - 20. If 5 is selected the calculation field will return my (@Net Total / 100 * 5). I have tried the below but obviously this is not correct.

if (@Tax Rate === "Zero Rated") {
(@NET Total / 100 * 0);
} else {
if (@Tax Rate === "1") {
(@NET Total / 100 * 1);
} else {
if (@Tax Rate === "2") {
(@NET Total / 100 * 2);
}


Appreciate the help in advance.

Thanks

Mike

Mike Davies

Please sign in to leave a comment.

11 comments

0
Avatar

Hi Mike,

I'm not sure if you are using a category or a relationship for the tax rate. I would suggest you use a relationship field.  This will be much easier to manage, and if at some point in the future the tax rate changes you can add a new tax rate item to the app, set the old one to be obsolete (via a category - then use a filtered view in your relationship field) and your calculation will always work. Also any old data will not be updated by the new tax rate formula

I'm guessing you have one tax rate referenced to the sale item. Also the tax rate item should have the tax rate assigned to it in a field (lets call that field Tax Rate).  I assume the sale item has a field for Sale Price

in this case something like this should work

@Sale Price * @Sum of Tax Rate / 100

Let me know more detail around your configuration if this doesn't help

Kent Watson 0 votes
0
Avatar

Thank you for your assistance, could you please assist with the calculation if it was a category?

Mike Davies 0 votes
0
Avatar

Hi Mike,

try this

var tax = 0;
if (@Tax Rate == "1") {
tax = @NET Total / 100 * 1;
} else if (@Tax Rate == "2") {
tax = @NET Total / 100 * 2;
} else { //the last condition does not need an if. this will also mean if no tax is selected the result is zero
tax = @NET Total / 100 * 0;
};
tax;

 

Refer here for more information on a else if conditions

Kent Watson 0 votes
0
Avatar

Hi Mike,

maybe this shorter one works too if tax rate is a category field with categories like:
Zero Rated
1
2
3

var tr = @tax rate;
var tr = tr == "Zero rated" ? 0 : Number(tr);
@NET Total / 100 * tr

Works also if you use a relationship field as Kent recommended.

Rainer

Rainer Grabowski 0 votes
0
Avatar

Hey that's nice Rainer would you be so kind as to explain that middle line of code. I don't find any syntax like that on W3schools, maybe I just don't know where to look

Kent Watson 0 votes
0
Avatar

Hi Rainer/ Kent,

Thank you both for your help, really appreciated.

I am a complete beginner with this and I am struggling with the second calculation. I have attached screenshots of how I have input and the error which is returned. If you could please feedback on the calculation that would be extremely helpful. It will be useful for me to understand both ways.

I look forward to your response.

Many thanks

Mike




 

Mike Davies 0 votes
0
Avatar

Hi Mike,

it must be Number not number  - N not n.

@Kent
That's called Shorthand IF/ELSE

tr == "Zero rated"  -> IF-Condition
? -> then
: -> else
you always need both (? and : )

Rainer

Rainer Grabowski 0 votes
0
Avatar

Hi Rainer. Would the calc work if on the Tax category field we simply put: NO and YES, words and no numbers, as in your setup, i.e. NO will give us $0.00 tax, and YES will give us the 8.875% of the total? Thanks as always.

Gus H. 0 votes
0
Avatar

Hi Gus,

If you want to do: 

@NET Total / 100 * tr

no, you need a number as tr or you have to oparse the word 8string) to number. 

you can try this:

var tr = @tax rate;
var tr = tr == "No" ? 0 : 8.875;
@NET Total / 100 * tr
Rainer Grabowski 0 votes