4 Replies Latest reply on Jul 28, 2016 5:17 PM by philmodjunk

    SUM() Usage for Single Table


      I have been playing with aggregation functions and don't seem to be getting the hang of how it works, when trying to aggregate all records in a single table for a single field.


      Basically I have a field that allows users to enter a project closing amount ($) and I would like to aggregate all the entries and show it as a total sum.  I am trying to avoid having to store the value in another table or do joins as I am not a fan of ghosting.


      Here is the script I have:


      Set Variable [$$TotalDeals ; Value Get( FoundCount )]

      Set Variable [$$TotalRevenueProjected ; Value Sum(t blOpportunities::ProjectedCloseAmount )]

      Set Variable [$$AverageCloseProbability ; Value Average( tblOpportunities::CloseProbablityPercent )]

      Refresh Window [ ]


      The Get(FoundCount) works fine and updates the variable.  The Sum and Average functions do not work as I expected but was hoping I was doing something silly - which is more then likely the case - and someone could point me in the right direction.


      At the end of the day, I really just want to know the total $ amount projected - which will be displayed on a dashboard using a MergedVariable.


      Thank you for your help in advance.