Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be like
Dimension : Position
Expression: AVG(AGGR(SUM(Prod),Year,Month))
hth
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
ID | Visitdate | Client | Service | Staffid | provider | cpt | program | trasnfer_date | Prod | emp_status | Position |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 01/01/2017 | Doe, Jane | Install | 5 | Sue, Sally | A101 | Security | 01/03/2017 | 1000 | A | Sales Leader |
2 | 01/01/2017 | Doe, Wallace | Upgrade | 8 | Save, Frank | A106 | Sales | 01/02/2017 | 500 | A | Account Rep |
3 | 01/05/2017 | Doe, Jack | Repair | 5 | Sue, Sally | B115 | Security | 01/08/2017 | 250 | A | Sale 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:
Position | Year to Date Prod | Monthly Avg |
---|---|---|
Sales Leader | 12000 | 1000 |
Account Rep | 10000 | 833 |
Installation Tech | 7000 | 583 |
Recovery Expert | 10000 | 833 |
May be like
Dimension : Position
Expression: AVG(AGGR(SUM(Prod),Year,Month))
hth