0 Replies Latest reply: Jul 21, 2011 12:29 PM by Ben Reed RSS

    Need to Perform two Calculations in QVD load ... Loop?

    Ben Reed

      I am trying to arive at a summary data set for work performed in our facility.  Ulitmately what i would like to have is an Avg Manhours, Turn Around Time, Average Material Cost, and Average Material Cost per Day (=Avg Mat. Cost/Turn around Time).  I would like to do this without having to create multiple QVDs which then need to be loaded and calcuated upon in order to eventually arrive at this data set.  Is this possible? can it be done with a loop?  I have pasted my script below and it is not running because nested aggregation is not allowed.  That is because i am trying to Sum the Averages to arrive at a total material cost average per WO_DATA_CODE.   (SUM(AVG(SUM_C_COST)+AVG(SUM_S_COST)+AVG(SUM_R_COST)) as MATERIAL_COST.  Any help in performing the calculation in bold and underlined in the script below would be appreciated.

      Thanks,

      B

       

       

       

      LOAD

      WO_ORG_CODE&'_'&WO_GROUP_PRIMARY&'_'&WK_TYPE AS WO_DATA_CODE,

      WO_GROUP_PRIMARY, WO_ORG_CODE, ROUND(AVG(HRS),.1) AS AVG_HRS,

      AVG(HRS),

      AVG(SUM_C_COST),

      AVG(SUM_S_COST),

      AVG(SUM_R_COST),

      SUM(AVG(SUM_C_COST)+AVG(SUM_S_COST)+AVG(SUM_R_COST)) as MATERIAL_COST,

      IF(ROUND(AVG([DAYS WORKED]),1)<1,1,ROUND(AVG([DAYS WORKED]),1)) AS TAT,

      AVG([DAYS WORKED])

       

      FROM .\QVD\ARA_Completed_Work_Orders.qvd (qvd)

       

      WHERE

      WO_POSITION_CODE ='SHOP'

      and [LAST WK YEAR] > YEAR(ReloadTime())-4

       

      GROUP BY

      WO_ORG_CODE&'_'&WO_GROUP_PRIMARY&'_'&WK_TYPE,

      WO_GROUP_PRIMARY, WO_ORG_CODE;