Weighted Average in a Pivot Table

[Caleb_Garascia]

Hello!

I am trying to do a weighted average in a Pivot table with 3 dimensions and date across the top (year, month, week, date)

I am having trouble getting the calculations to work.  At a high level I have Job groups, Job Codes, and People for my dimensions.  The goals are different per job code, so I am trying to calculate the 'True' goal based on hours spent in each job code.

IE  Job Code A has a goal of 25  and I spent 25% of my time there and Job Code B has a goal of 40 and I spent 60% of my time there.  Meaning my goal would be the weighted average of the two (6.25+30 =36.25)

I am able to calculate the weights correctly, however getting the multiplication of the goal  to work and THEN add them up, I cant get that piece to work UNLESS I filter down to a date range and make the date just one column.

If you expand out Year it breaks.  UPDATE: I was able to find an expression that is correct for any particular column date hierarchy, but not in general.  (IE I can write an expression that works if the end user is looking at particular level [month, week, year, or date]).

Below is my expression:
if(dimensionality() = 0,
SUM(AGGR(SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<TOTAL_YEAR> TIME_ON_TASK_SECONDS))*SUM(GOAL),TOTAL_YEAR,TOTAL_JOB_CODE)),TOTAL_JOB_GROUP,TOTAL_YEAR,TOTAL_MONTH,TOTAL_WEEK, TOTAL_DATE)),
if(dimensionality() = 1,
SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<TOTAL_YEAR,TOTAL_JOB_GROUP> TIME_ON_TASK_SECONDS))*SUM(GOAL),TOTAL_JOB_CODE,TOTAL_JOB_GROUP,TOTAL_YEAR,TOTAL_MONTH,TOTAL_WEEK, TOTAL_DATE)),
if(dimensionality() = 2,
SUM(GOAL),
if(dimensionality() = 3,
AVG(GOAL)
))))


I am focused on Dimensionality 0 and 1 atm.

[Caleb_Garascia · Accepted solution]

I was able to get this to work in a pivot table, but only with one level of date at a time.
ended up having to make a button that change the date to only show the year, month, week, date, etcc...

Below is the function, the non-date variables are needed because I allow the end user to see the data at the job code level or the person level.  Otherwise they are not needed.  The date variable IS required however.  All it does is: if the end user selects year it will put in the year dimension in the expression. Same with all the other levels of dates you might need.

The if checks within the dimensionality checks are only required because I allow the end user to select how to view the average (again either by person or job code).

Hopefully this will be helpful to someone.

if(dimensionality() = 0,
SUM(AGGR(SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<$(vDateDim)> TIME_ON_TASK_SECONDS))*SUM(GOAL),$(vDateDim),$(vTier2Dim))),$(vTier3Dim),$(vDateDim))),
if(dimensionality() = 1,
if(vVASDimToggle = 1,
SUM(AGGR(SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<$(vDateDim)$(vTier1Dim)> TIME_ON_TASK_SECONDS))*SUM(GOAL),$(vDateDim),$(vTier2Dim)$(vTier1Dim))),$(vTier3Dim),$(vDateDim)$(vTier1Dim)))
, SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<$(vDateDim),$(vTier3Dim)$(vTier1Dim)> TIME_ON_TASK_SECONDS))*SUM(GOAL),$(vTier3Dim),$(vTier2Dim),$(vDateDim)$(vTier1Dim)))
),
if(dimensionality() = 2,
if(vVASDimToggle = 1,
SUM(AGGR((Sum(TIME_ON_TASK_SECONDS)/SUM(TOTAL<$(vDateDim),$(vTier3Dim)$(vTier1Dim)> TIME_ON_TASK_SECONDS))*SUM(GOAL),$(vTier3Dim),$(vTier2Dim),$(vDateDim)$(vTier1Dim)))
, SUM(GOAL)),
if(dimensionality() = 3,
AVG(GOAL)
))))