3 Replies Latest reply on Jul 25, 2015 11:01 AM by philmodjunk

    Formula not returning all intended components

    LennyGare

      Title

      Formula not returning all intended components

      Post

      The goal of this formula is to return a percentage for each investment for each investor of how much of the total invested in that series is.  Say Investor A has invested in Company A in Series A, B, C, and D.  It should provide me with the percentage of the total investment for each investment in each series. Currently the percentage showing up for every investment in each company is the same throughout the company. Even when the amounts invested in each individual series are different. For the most part these percentages are accurate to one of the series'.  Does anything stand out that I'm doing wrong?

       

      If(S4_BVD Sub Series Number = 1 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 1 ; S4_Investor_Entities 1::S4_Inv Amount 1 / S3_Investments::S3_Series Investment Total 1; "" ) &
      If(S4_BVD Sub Series Number = 2 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 2 ; S4_Investor_Entities 2::S4_Inv Amount 2 / S3_Investments::S3_Series Investment Total 2; "" ) &
      If(S4_BVD Sub Series Number = 3 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 3 ; S4_Investor_Entities 3::S4_Inv Amount 3 / S3_Investments::S3_Series Investment Total 3; "" ) &
      If(S4_BVD Sub Series Number = 4 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 4 ; S4_Investor_Entities 4::S4_Inv Amount 4 / S3_Investments::S3_Series Investment Total 4; "" ) &
      If(S4_BVD Sub Series Number = 5 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 5 ; S4_Investor_Entities 5::S4_Inv Amount 5 / S3_Investments::S3_Series Investment Total 5; "" ) &
      If(S4_BVD Sub Series Number = 6 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 6 ; S4_Investor_Entities 6::S4_Inv Amount 6 / S3_Investments::S3_Series Investment Total 6; "" ) &
      If(S4_BVD Sub Series Number = 7 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 7 ; S4_Investor_Entities 7::S4_Inv Amount 7 / S3_Investments::S3_Series Investment Total 7; "" )

        • 1. Re: Formula not returning all intended components
          philmodjunk

          Your calculation repeats the same basic calculation 7 times. I would test and debug one of these at a time. Once you can get all 7 parts to work correctly, you can then combine them into a single calculation. In terms of why you might be getting a wrong result, one of your fields is coming from S4_investor_Entities, so I would carefully check to make sure that your relationship is valid and that the references to this field from a related table are accessing the correct record and thus bringing the correct value into the calculation.

          Another observation is that it appears that you are using multiple fields where you are likely better off with just one field and multiple related records. That's suggested by your reference to 7 investment date fields in the same record.

          • 2. Re: Formula not returning all intended components
            LennyGare

            This is what the formula looks like now :

            If(S4_BVD Sub Series Number = 1 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 1 ; S4_Investor_Entities 1::S4_Inv Amount 1 / S3_Investments::S3_Series Investment Total 1; "" ) &
            If(S4_BVD Sub Series Number = 2 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 2 ; S4_Investor_Entities 2::S4_Inv Amount 2 / S3_Investments::S3_Series Investment Total 2; "" ) &
            If(S4_BVD Sub Series Number = 3 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 3 ; S4_Investor_Entities 3::S4_Inv Amount 3 / S3_Investments::S3_Series Investment Total 3; "" ) &
            If(S4_BVD Sub Series Number = 4 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 4 ; S4_Investor_Entities 4::S4_Inv Amount 4 / S3_Investments::S3_Series Investment Total 4; "" ) &
            If(S4_BVD Sub Series Number = 5 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 5 ; S4_Investor_Entities 5::S4_Inv Amount 5 / S3_Investments::S3_Series Investment Total 5; "" ) &
            If(S4_BVD Sub Series Number = 6 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 6 ; S4_Investor_Entities 6::S4_Inv Amount 6 / S3_Investments::S3_Series Investment Total 6; "" ) &
            If(S4_BVD Sub Series Number = 7 and S4_BVD Entity Name = S3_Investments::S3_Entity Name and S4_Investment Date = S3_Investments::S3_Company Investment Date 7 ; S4_Investor_Entities 7::S4_Inv Amount 7 / S3_Investments::S3_Series Investment Total 7; "" )

             

            I am in a much better place than I was previously, but I'm stuck on something else. The layout that I am working on is for an individual investor and all of their related investments.  At this point I am getting different percentage values for each investment, versus having the same percentage all the way down the line. My issue now is that it is not always taking the value related to that specific investor. All of the percentages are valid (at least one investor for that company has the percentage displayed), but it is not always the right investor.  The first percentage for each company is always accurate, and for one of them the third percentage is correct, but all of the others are from a (seemingly) random investor in that entity. 

             

            I am really confused by this because this should be specified by "S4_Investor_Entities 1::S4_Inv Amount 1" "S4_Investor_Entities 2::S4_Inv Amount 2" "S4_Investor_Entities 3::S4_Inv Amount 3" etc.. Any ideas?

            • 3. Re: Formula not returning all intended components
              philmodjunk

              This sentence contradicts itself:

              All of the percentages are valid (at least one investor for that company has the percentage displayed), but it is not always the right investor.

              If the relationship were correct, it would match to the correct investor.

              I must point out that you have not spelled out your data model. I have no way to tell (crystal ball is out for find tuning) what relationship you currently have in place and what match fields you have set up for those relationships. Unless you carefully describe them or post a screen shot of the relevant portion of Manage | Database | relationships, I can't really suggest anything on how to access the correct data for your calculation.