3 Replies Latest reply on Nov 20, 2015 8:55 AM by sydbarker

    Calculation Error?

      Lump SumQty$ DollarsQty %$ Dol
      2015 Lump Sum Cancelled1$90,0002.2%0.2%
      2015 Lump Sum Lost20$10,584,75844.4%18.6%
      2015 Lump Sum No-Bid0-0.0%0.0%
      2015 Lump Sum On-Hold0-0.0%0.0%
      2015 Lump Sum Pending43,832,2258.9%6.7%
      2015 Lump Sum Won1110,306,67224.4%18.1%
      2015 Lump Sum Working931,978,82020.0%56.3%


      What I am trying to do is create calculations to mimic a table similar to this. Here is the calculation I created, that is not necessarily working properly;

      If ((Year(OriginalDueDate) = Year(Get(CurrentDate))) & (BidType = "Lump Sum") & (Status = "Lost"); 1; 0)


      And so on and so forth for each type, as well as a summary field total of the calculation. Here is an example of what data is returned, and there are currently only two records, one being from 2015 and one test record of a 2015 Lump Sum Cancelled bid.The dollar values being returned are accurate to the one record for 2015, but that amount is only for category "cancelled." The value of 62,485,618 is incorrect, and does not match either of the two records.


      I know this is probably a simple fix, but nonetheless, I'm stuck. any tips?


      Lump SumQty$ Dollars
      2015 Lump Sum Cancelled11,000,000
      2015 Lump Sum Lost11,000,000
      2015 Lump Sum No-Bid11,000,000
      2015 Lump Sum On-Hold11,000,000
      2015 Lump Sum Pending11,000,000
      2015 Lump Sum Won262,485,618
      2015 Lump Sum Working11,000,000
        • 1. Re: Calculation Error?

          Hi Syd,


          Try changing "&" to "and"  (without the quotes).   


          If ((Year(OriginalDueDate) = Year(Get(CurrentDate))) and (BidType = "Lump Sum") and (Status = "Lost"); 1; 0)

          • 2. Re: Calculation Error?

            & is a text operator

            Text operators


            You need a logical operator

            Logical operators

            • 3. Re: Calculation Error?

              Thank you guys so much, that worked perfectly. Now in order to create the percentage, this is the calculation I have;


              If (

              (Year(OriginalDueDate) = Year(Get(CurrentDate))) and (BidType = "Lump Sum") and (Status = "Working"); (LumpLostTotal*100)/LumpSumCountT; 0)


              Where LumpLostTotal is a summary total of

              If ((Year(OriginalDueDate) = Year(Get(CurrentDate))) and (BidType = "Lump Sum") and (Status = "Lost"); 1; 0)

              And LumpSumCountT is a total summary of

              If (

              (Year(OriginalDueDate) = Year(Get(CurrentDate))) and (BidType = "Lump Sum"); 1; 0)

              In order to return a grand total of the Lump Sum Qty category.


              If these calculations were to perform correctly, my result for 2015 Lump Sum Working should return 100%, considering it is the only test record for 2015.