Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
erickd1190
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

May be like

Dimension : Position

Expression: AVG(AGGR(SUM(Prod),Year,Month))

hth

View solution in original post

3 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi Erick,

Could you give a small fragment of the original data in a tabular form and what do you want to get at the output?

Regards,

Andrey

erickd1190
Contributor III
Contributor III
Author

IDVisitdateClientServiceStaffidprovidercptprogramtrasnfer_date

Prod

emp_statusPosition
101/01/2017Doe, JaneInstall5Sue, SallyA101Security01/03/20171000ASales Leader
201/01/2017Doe, WallaceUpgrade8Save, FrankA106Sales01/02/2017500AAccount Rep
301/05/2017Doe, JackRepair5Sue, SallyB115Security01/08/2017250ASale Leader

I am looking to get a line chart that shows the average sales per month by position.

So in tabular form it would look like this:

PositionYear to Date ProdMonthly Avg
Sales Leader120001000
Account Rep10000833
Installation Tech7000583
Recovery Expert10000833
sasiparupudi1
Master III
Master III

May be like

Dimension : Position

Expression: AVG(AGGR(SUM(Prod),Year,Month))

hth