# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New 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:

date,

id,

client,

service,

staffid,

provider,

cpt_code,

program,

transfer_date,

"rate" AS Prod,

emp_status

FROM History.QVD

Employee ID,

Last Name First,

EmployeeStatus,

Site,

Last Hire Date,

Program,

Position Description,

Program-Position Description,

Supervisor Name,

CorporateLevelCode,

Termination Date,

PT,

IP

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,

*;

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
Honored Contributor III

## Re: AVG(AGGR(SUM(Monthly Prod),)) not correct?

May be like

Dimension : Position

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

hth

3 Replies
Honored Contributor

## Re: AVG(AGGR(SUM(Monthly Prod),)) not correct?

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

New Contributor III

## Re: AVG(AGGR(SUM(Monthly Prod),)) not correct?

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
Account Rep10000833
Installation Tech7000583
Recovery Expert10000833
Honored Contributor III

## Re: AVG(AGGR(SUM(Monthly Prod),)) not correct?

May be like

Dimension : Position

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

hth