Please help with multiple condition calculation that i want to put into single field.

[Mark Pastoril]

Good day maam and sir,

I would like to ask for help about this calculation?if how can I use and apply this one in to one field?

This is for water billing app,

Calculation is :

Previous Reading-Present Reading=Consumption

Consumption =Amount Due

The Consumption is calculated result from below calculations.

But it is depend if the Connection Type is Residential,Commercial or Bulk.

 

If type is Commercial “C”

Case(Consumption>=36;(((((Consumption-15)-10)-10)*21.56)+43.13+945);If(Consumption>=26;((((Consumption-15)-10)*34.5)+600);If(Consumption>=16;(((Consumption-15)*30)+300);If(Consumption>=15;(300);If(Consumption>=10;(300);If(Consumption<=9;Consumption*30;"0"))))))

 

If type is residential “R”

Case(Consumption>=36;(((((Consumption-15)-10)-10)*21.56)+472.5);If(Consumption>=26;((((Consumption-15)-10)*17.25)+300);If(Consumption>=16;(((Consumption-15)*15)+150);If(Consumption>=15;(150);If(Consumption>=10;(150);If(Consumption<=9;Consumption*15;"0"))))))

If Connection Type is Bulk

“B”=Case(Consumption>=1;Consumption*200)

Edited November 28, 2019 by Mark Pastoril

[Steve Martino]

I don't know where all the numbers come from, but if they are rates, they should be in a separate Rates table so they can be looked up and changed without modifying historical data.

Also, too many parenthesis.  Much of your logic makes no sense, for example in 'Commercial' if consumption is greater or = to 16 is ok, but then you want to know if it's greater or = to 15 and greater or = to 10 have the same result.  Then less than or equal to 9 has a result, but I see no reason for '0'.  Because a zero or negative number is less than 9.

 

Anyway this is easier to read and can be streamlined even further:

Let([
   @C=Consumption;
   @calc=Case(Type="Residential";
                    Case(@C>=36;(@C-15-10-10)*21.56+472.5;
                         @C>=26;(@C-15-10)*17.25+300;
                         @C>=16;(@C-15)*15+150;
                         @C>=10;150;
                         @C*15) 
                         ;
               Type="Commercial";
                    Case(@C>=36;(@C-15-10-10)*21.56+43.13+945;
                         @C>=26;(@C-15-10)*34.5+600;
                         @C>=16;(@C-15)*30+300;
                         @C>=10;300;
                         @C*30)
                         ;

               Type="Bulk";
                     Case(@C>=1;@C*200;0)
              ) //end case
           ];  //end variables
@calc
)

I'll assume all the "-15-10-10" are different rate classes or discounts applied, but like I stated earlier, they should be in a Rates table.  If you were just doing this to see if the calc works, then you can declare each one of those fields in a variable for a cleaner, easier to read calc.

Edited November 29, 2019 by Steve Martino

[Wim Decorte]

I would probably go one step further and put this logic in a script that gets executed as part of the user's workflow when the 'application' is set up or submitted.  The IF... ELSE IF logic will be generally easier to follow and much easier to troubleshoot.

[Mark Pastoril]

Thanks for helping, this solves my problem. I will try to put this in other table.