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

    SUM() Usage for Single Table

    user26705

      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.