2 Replies Latest reply on Jan 10, 2013 10:26 AM by Robin Kennedy

    Calculated field help


      I hope I"m able to make sense of this.  We are transitioning from using an excel sheet to connecting live with our data and I'm running into a scenario that I'm unable to figure out.


      Say we have 10 claims, each with a variety of defendants.  We set a reserve value for each defendant in the claim.  I want to create a calculated field showing the total reserve for each claim (so I can filter it to show only claims that exceed "x" amount). 


      For example, Claim XYZ has 3 defendants.  Each defendant has a reserve of $50,000, making the total claim reserve $150,000.  If I were to set a filter showing claims with reserve over $100,000, this claim would not show up at all.  How can I set up a calculated field to show this claim as $150,000?  I'm assuming once I'm able to that, I could set up the report and it will show all 3 defendants, as well as $50,000 reserve for each.


      Any help someone would be able to provide would be much appreciated.



        • 1. Re: Calculated field help
          Mark Holtz

          Hi Brant,


          How is your underlying data set up? 1 row per claim? 1 row per defendant? It sounds like you have 1 row per defendant, if $50,000 value is calculated at a row level--which is where basic filtering takes place.


          Can you mock up some dummy data, or share a packaged workbook?

          • 2. Re: Calculated field help
            Robin Kennedy



            You need to set up a new calculated field that sums the total reserve and then configure it to compute using defendant, for each claim separately.


            Create a calculated field with the formula


            TOTAL( SUM( [Reserve]) )


            and add it to your view. From there, change the 'Compute Using' option (right click on the field's pill) to Defendant. You should now see the total reserve per claim.


            Now copy this field to the filters shelf (hold down Ctrl as you move it with the mouse) and select At Least 10000


            Now you should just see Claims where defendant's reserves total atleast 10k.


            I have attached a sample workbook doing a similar thing for you.