AnsweredAssumed Answered

Creating a Calculation Field with Different Functions

Question asked by DanaSadowski7326 on May 22, 2013
Latest reply on May 22, 2013 by philmodjunk

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?

Outcomes