1 Reply Latest reply on May 22, 2013 9:56 PM by philmodjunk

    Creating a Calculation Field with Different Functions

    DanaSadowski7326

      Title

      Creating a Calculation Field with Different Functions

      Post

           I have a database that contains orders from several different manufacturers.  I have orders and mfg tables and created the relationships between them.  We have quotas that the mfgs give us so I have a layout that shows the quota and want to show the actual amount ordered by month.  Can I have the "actual" field calculate just the orders for a particular mfg AND a particular date range.  I've figured out how to get it to pull just the mfgs orders (using a case function) but can I also have it pull a date range as well?  I've tried the date range function (DateRange ( startDate ; days ) but I have a feeling I'm missing something stupid about where in the calculation it goes.  Here's what the calculation looks like so far:

           Case ( _kpt_Mfg_ID = 1001 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1002 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1003 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1004 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1007 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1008 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1011 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1012 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1013 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1016 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1017 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1019 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1027 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1031 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1032 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1034 ; Sum ( ORDER::zz__Total__xn ) ;
                      _kpt_Mfg_ID = 1035 ; Sum ( ORDER::zz__Total__xn ) )
           This calculation returns the appropriate orders for each mfgs record, but it is totalling every order in the orders layout.  I can't figure out how to include a date range (i.e. tota of orders shipped between 1/1/2013 and 1/31/2013)  What am I doing wrong?

        • 1. Re: Creating a Calculation Field with Different Functions
          philmodjunk

               This is actually a frequently asked quesiton in this forum. The question comes down to using one expression or another to try to selectively sum (or compute other aggregate values) data from a related table and as you have discovered, sum computes a total of all related records--not the desired subset of related record that meets some additional criteria such as a manufacturer's ID and/or a date range.

               There are several approaches that can be used to selectively sum a set of records, but using the sum function like this is not one of them.

               See this thread for a discussion of those othe methods: Sum_Calculation based on condition