Issue with Advanced Aggregation in Pivot - Weighted Average Headcount Totals

Tim Ouimet

The issue I'm having relates to calculating weighted daily average headcount - totalling by Qtr, Year, by Site.

The Data is stored at the Contract Grain, Monthly.

The Hierarchy looks like Site -> Customer -> Contract., Time is by Year -> Qtr -> Month.

Using our Business Model we're trying to build Weighted Hc - by month, totalled at Quarter, Year.

Should look like this:

Correct Data

Site	Jan	Feb	Mar	QTR
Location 1	272	265	262	266.4
Contract 1	180	169	168	172.4
Contract 2	92	96	94	94
Location 2	184	181	167	177.3
Contract 3	95	91	79	88.3
Contract 4	44	44	43	43.7
Contract 5	45	46	45	45.3
Total Locations	456	446	429	443.6
Days in Month	31	29	31	91

Total Days in Month is set as a SUM across the Calendar Dimension, and a MAX across the Company Dimension (Location, Contract etc..).

What is happening with my weighted totals - is that the Total Location is displaying the MAX at the Location Grain, but the Correct total at the Total Locations Line!

Incorrect Data

Site	Jan	Feb	Mar	QTR
Location 1	272	265	262	266.4
Contract 1	180	169	168	172.4
Contract 2	92	96	94	94
Location 2	272	265	262	266.4
Contract 3	95	91	79	88.3
Contract 4	44	44	43	43.7
Contract 5	45	46	45	45.3
Total Locations	456	446	429	443.6
Days in Month	31	29	31	91

The logic at the base table assumes that every day an employee is active, registers a 1 in the source table, so an employee would have 91 records in the detailed table for Q1'16.

The Summary Table has all the employee records summarized to 3 rows, one per month, joining to Calendar Month table grain.  Each record has 31, 29, 31 as the headcount respectively.

The source data is weighted - so that it does Num of Days * Headcount / number of Days.

sum(Fact COA Goals Monthly.PROJECTED_HEADCOUNT * Fact COA Goals Monthly.NUM_OF_DAYS) / nullif( max(sum(Fact COA Goals Monthly.NUM_OF_DAYS)) * 1 , 0)

Any ideas how to control the Site Totals grain weighted abilities with Aggregation Tab on a Logical Column?

Thanks,

Tim Ouimet