3 Replies Latest reply: Mar 17, 2017 2:10 PM by Sasidhar Parupudi RSS

    AVG(AGGR(SUM(Monthly Prod),)) not correct?

    Erick Dameron

      HI All,

       

      I am working on putting together a line chart that shows the average monthly production per position.

      The goal is to use this to analyse production targets and their relevance to each position.

      I am currently using the following Expression

      AVG(AGGR(SUM(Prod),))

      but getting inaccurate results.

       

      Should I be writing an expression for each position?

      With 12 departments and 10-15 positions per department that would take a while to write out.

       

       

      My table looks like this:

      History:

      LOAD

          date,

          id,

          client,

          service,

          staffid,

          provider,

          cpt_code,

          program,

          transfer_date,

          "rate" AS Prod,

          emp_status

      FROM History.QVD

       

       

      [Emp-Incentive-Link]:

      LOAD

        Employee ID,

        Last Name First,

        EmployeeStatus,

        Site,

        Last Hire Date,

        Program,

        Position Description,

        Program-Position Description,

        Supervisor Name,

        CorporateLevelCode,

        Termination Date,

        PT,

        IP

      FROM Emp-Incentive-Link

       

      Calendar:

      Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,

        Dual(Month, fMonth) AS FMonth,

          DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,

          *;

       

      Load Year + IF(Month>=$(vFM), 1,0) As fYear,

        Mod(Month-$(vFM),12)+1 As fMonth,

          DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,

         *;

      Load visitdate,

      Year (visitdate) As Year,

      Month (visitdate) as Month,

      Week (visitdate) as Week

       

       

      Resident History;

       

       

       

       

       

      I also tried creating a new field called TOTALPROD using a preceding load but keep getting a script error when executing.

       

      Any advice is greatly appreciated!

       

      Best,

       

      Erick