14 Replies Latest reply on Oct 12, 2013 5:35 AM by BorisKamp

    How to achieve this calculation?

    BorisKamp

      Title

      How to achieve this calculation?

      Post

           hi!

           In our property database we would like to keep track of rehab budgets and final rehab costs.

           We would like these outcomes:

             
      1.           Under budget (if final rehab costs are >10% under budgeted costs) (example: buget $4000 Final costs $3000)
      2.      
      3.           Budget (if final rehab costs are  within 10% of the budgeted costs) (example: buget $4000 Final costs $4200 or 3800)
      4.      
      5.           Over budget (if final rehab costs are >10% over budgeted costs) (example: buget $4000 Final costs $5300)

            

           We would like the the texts "Under Budget" "Budget" "Over Budget" to appear depending on the outcome of the calculation.

            

           I cant seem to figure this out so any help or links to documentation about this is very welcome.

            

           Thanks guys!

        • 1. Re: How to achieve this calculation?
          philmodjunk

               Am I correct that you can compute or enter values for Budget and Final costs?

               If so, a simple case function should suffice for returning the correct text based on how the two values compare:

               Let ( Pct = ( Budget - Final ) / Budget ;
                        Case ( Pct > 0.1 ; "Under " ;
                                     Pct < -0.1 ; "Over "
                                   )
                       ) & "Budget"

          • 2. Re: How to achieve this calculation?
            raybaudi
            
            
                 Case ( final rehab costs * budget ; // if none of the two fields is empty
                 Case (
                 final rehab costs < 95/100*budget ; "Under budget" ;
                 final rehab costs > 105/100*budget ; "Over budget" ;
                 "Budget"
                 )
                 )
            • 3. Re: How to achieve this calculation?
              BorisKamp

                   Thanks Phil! 

                   Do you have any refrence for me so I can learn how to implent this as well? I followed the Lynda course but this is still magic to me.

                    

                   I got a simlair question for another field:

                   We got two field:

                     
              1.           Expected sales price
              2.      
              3.           sales price (final)

                   Now we want the "Under Budget" "Budget" "Over Budget" story again but slightly different:

              with an expected sales price up to 50.000 we would like the same as in my first question (10%) but with everything above an expected sales price of 50.000 we would like 5% instead of 10%

              So if:

                     
              •           expected price is 50.000 and sales price is 54.000 = budget (10%)
              •      
              •           expected price is 50.000 and sales price is 58.000 = over budget (10%)
              •      
              •           expected price is 50.000 and sales price is 41.000 = under budget (10%)

              But:

                     
              •           expected price is 90.000 and sales price is 94.000 = budget (5%)
              •      
              •           expected price is 90.000 and sales price is 97.000 = over budget (5%)
              •      
              •           expected price is 90.000 and sales price is 81.000 = under budget (5%)

              You get it right?

              Thanks for helping us out! we really appreciate it!

                    

                    

              • 4. Re: How to achieve this calculation?
                philmodjunk

                     The original suggest calculation (or Raybaudi's version) is implemented exactly as written. The case function is a function that you can look up in FileMaker Help to learn more about it.

                     I get the basic idea of what you are trying to do, but the devil is in the details. Your example shows two "tiers" of values, but might you have 3, 4 or more? And as real estate values change over time won't you need to adjust the value ranges where you want to apply these different percentages?

                • 5. Re: How to achieve this calculation?
                  BorisKamp

                       Hi!

                       Yeah, for now we have two tiers of values 0>50.000 and >50.000 In the future these tiers might, like you say, change, but we can adjust the formula right?

                       Or do you have a better way?

                       Thanks for helping!

                  • 6. Re: How to achieve this calculation?
                    philmodjunk

                         Yes, you can adjust the formula. But why should you want to? That requires the person with "Full access" to open the database in Manage | Database | Fields and edit the calculation. That then locks down the entire table from changes until the change is committed.

                         But an alternative approach can set this up where you can change the amounts by editing a record on a layout--something that does not require the developer to do nor does it "lock" the table while you are making this change.

                         What I am suggesting is that you set up a related table where each record is one "tier" of these values. Right now, that's just two records in the table, but if you later decide that you need to add more, you can just add another record to the table. Fields in these related tables would then record the minimum purchase price (0 and 50.000 in your current case ) along with the Percentage value that applies for that tier.

                         Your relationship would look like this:

                         MainTable::Amount > BudgetMessage::MinAmount

                         And in the Edit Relationship dialog that you can open up by double clicking the relationship line, you specify that the BudgetMessage table records are sorted by MinAmount in Descending order.

                         Then you can use my calculation or Raybaudi's but modified to reference data in the BudgetMessage table:

                         Let ( [ Pct = ( Budget - Amount ) / Budget ;
                                   Lim = BudgetMessage::Pct
                                 ] ;
                                  Case ( Pct > Lim ; "Under " ;
                                               Pct < (-1*Lim)  ; "Over "
                                             )
                                 ) & "Budget"

                    • 7. Re: How to achieve this calculation?
                      BorisKamp

                           Sounds like a great way Phil!

                           Unfortunately I do not exactly understand what youre trying to explain.

                             
                      1.           So I would create a table called "Expected Sales Result" lets call, it "ESR" in this post. in the ESR table I create three fields "_fkESRID" "MinAmount" and "AmountPct"
                      2.      
                      3.           I need to relate the fk field to the field of my properties table main table where the ESR budget result needs to be shown.
                      4.      
                      5.           And in the Edit Relationship dialog that you can open up by double clicking the relationship line, you specify that the ESR table records are sorted by MinAmount in Descending order.
                      6.      
                      7.           In the ESr Table I enter four records in total: MinAmount 0 ; 50.000 and AmountPct 5% ; 10%. How does filemaker know which % goes with the 0 or 50.000 amount?
                      8.      
                      9.           in my properties table (main table) field where the under budget, budget or over budget message needs to be shown I enter the following:

                      Let ( [ Pct = ( Properties::Budget - Properties::Amount ) / Properties::Budget ;

                                Lim = ESR::Pct
                                   ] ;
                                    Case ( Pct > Lim ; "Under " ;
                                                 Pct < (-1*Lim)  ; "Over "
                                               )
                                   ) & "Budget"

                      now Im missing the Pct pieces right? what should I enter for those?

                      Thanks!

                      • 8. Re: How to achieve this calculation?
                        philmodjunk

                             2. Nope and this field is both mislabeled (either it should start with pk or it should not be named "ESRID") and not needed to get the results we need here.

                             I already specified this relationship: (but I'm renaming it here to match the data from your last post:)

                             Properties::Amount > ESR::MinAmount

                        now Im missing the Pct pieces right? what should I enter for those?

                             No, this is not the case. In the expression, PCT is computed from your data and Lim (for limit) is looked up from the PCT field in the ESR table.

                        • 9. Re: How to achieve this calculation?
                          BorisKamp

                               Thanks Phill, I got the calculation to work but did not manage to get the difference between the 0-50.000 dollar range and the >50.000 dollar range. Both ranges work with the 10% rule.

                               I added two entries in the ESR table:

                                 
                          •           MinAmount 0 ; AmountPct 5%
                          •      
                          •           MinAmount 50.000 ; AmountPct 10%

                               I made the following relationship: Properties::SalesInfoSalesResult > ESR::MinAmount

                          I added the following calc into the Properties::SalesInfoSalesResult field:

                          Let ( Pct = ( SalesInfoExpectedSalesPrice - SalesInfoSoldPriceGross ) / SalesInfoExpectedSalesPrice ;
                                   Case ( Pct > ,1 ; "Under " ;
                                                Pct < -,1 ; "Over "
                                              )
                                  ) & "Budget"

                               Can you notice what Im doing wrong or do you need more info?

                                

                                

                          • 10. Re: How to achieve this calculation?
                            philmodjunk

                                 Did you specify a sort order in the relationship? It needs to be sorted by MinAmount in descending order and this is specified in Manage | Database | Relationships.

                            SalesInfoSalesResult is the wrong field to use in the relationship. You should use SalesInfoSoldPriceGross as the match field.

                            • 11. Re: How to achieve this calculation?
                              BorisKamp

                                   Ok thanks, I changed it to the SalesInfoSoldPriceGross field.

                                   Yes I did sort the ESR table by MinAmount in desc order...

                                   Thanks for helping!

                                    

                              • 12. Re: How to achieve this calculation?
                                BorisKamp

                                     Hi Phil, 

                                     Do you know the solution to my problem?

                                     As mentioned above I did the sorting and changed the relationship, but still did not manage to get the difference between the 0-50.000 dollar range and the >50.000 dollar range. Both ranges work with the 10% rule.

                                Thanks!

                                • 13. Re: How to achieve this calculation?
                                  philmodjunk

                                       Your preceding post dated September 19, indicated what you did, but did not tell me that this was still not working for you.

                                       In reviewing this thread, it doesn't appear that you are using the correct calculation as the most recent example of what you are using does not match what I suggested:

                                  Let ( [ Pct = ( Properties::Budget - Properties::Amount ) / Properties::Budget ;

                                            Lim = ESR::Pct
                                               ] ;
                                                Case ( Pct > Lim ; "Under " ;
                                                             Pct < (-1*Lim)  ; "Over "
                                                           )
                                               ) & "Budget"

                                       While I haven't checked the math carefully, the relationships do seem to return the correct values as shown in this demo file: https://dl.dropboxusercontent.com/u/78737945/RealEstateDemo.fmp12

                                  • 14. Re: How to achieve this calculation?
                                    BorisKamp

                                         Thanks Phil! you're a filemaker hero!

                                         I adjusted my formula to yours and now it works great!

                                         Thanks again!