Posted: 4/23/2012
I am having an issues with a sales cube that I am building. The idea is to have one dimension that has employee organizational data and another dimension that has our fiscal calendar. The twist here is that at the leaf level of the organizational hierarchy we it is not Employee ID it is fiscal week. So the hierarchy looks like this
Sales Group -> Sales Team -> Sales Employee ID -> Employee Fiscal Week (Employee ID / Fiscal Week combo key).
The result is that when someone drills down to the employee they can get a week by week roll up of the employee sales history. The date dimension is a standard date hierarchy Fiscal Year -> Fiscal Quarter -> Fiscal Month -> Fiscal Week.
Now here is my dilemma. I am trying to build a calculated member that will show me the trailing twelve week sales for that employee for that week. So each week the trailing twelve week sales number would be different. Here is my current calculation:
CREATE MEMBER CURRENTCUBE.[Measures].T12WkBR
So a few things happen:
1 This works at the employee id level but not the employee fiscal week level but only if I filter by one week.
2 In excel pivot the Employee Dimension is no longer filtered by the date dimension and I every week for every employee but thee sales fact data is blanks except for the week filtered.
3 At the employee fiscal week level the calculation simply divides the sales amount by 12
4 If I filter by more than one week the numbers blow up and are way over. Here is what the Excel pivot data looks like to illustrate the problem.
4 If I filter by more than one week the numbers blow up and are way over.
Here is what the Excel pivot data looks like to illustrate the problem.
Looking for help any input would be great! Thanks Sam.
Posted: 4/24/2012
How is the Org dimension related to the fact table?
There is a key called EmployeeHistoryKey. It is the primary key on the Org Dimension. I made some progress yesterday by changning the calculated member to this
AS AVG({TAIL(LASTPERIODS(12, [Current Teams].[Current Team Structure].CurrentMember), 12)} ,AVG({TAIL(LASTPERIODS(12, [Dates].[Fiscal Calendar].CurrentMember), 12)}
AS AVG({TAIL(LASTPERIODS(12, [Current Teams].[Current Team Structure].CurrentMember), 12)}
,AVG({TAIL(LASTPERIODS(12, [Dates].[Fiscal Calendar].CurrentMember), 12)}
,[Measures].[Total Brokerage]))
The weekly broker record comes back exactly the way I want it but it still shows data for the other weeks even though I am filtering by fiscal week 11. Also if I add a second fiscal week to the filter the data is wrong. I highlighted in green where it showed up the way I wanted. Fiscal Calendar 11 Row Labels Total Sales T12WkBR Sales Team 1 $99,382 107629.7778 Broker 1 $2,255 3389.495 Wk52-11 : Broker 1 2600 Wk1-12 : Broker 1 2350 Wk2-12 : Broker 1 3155.666667 Wk3-12 : Broker 1 3724 Wk4-12 : Broker 1 3848.3 Wk5-12 : Broker 1 3861.083333 Wk6-12 : Broker 1 3575.5 Wk7-12 : Broker 1 3567.985 Wk8-12 : Broker 1 3483.416667 Wk9-12 : Broker 1 3550.394 Wk10-12 : Broker 1 3492.630909 Wk11-12 : Broker 1 $2,255 3389.495 Wk12-12: Broker 1 3461.267273 Wk13-12 : Broker 1 3597.394
The weekly broker record comes back exactly the way I want it but it still shows data for the other weeks even though I am filtering by fiscal week 11. Also if I add a second fiscal week to the filter the data is wrong. I highlighted in green where it showed up the way I wanted.
I figured it out!! I still have to handle the calculation differently at the employee roll up level which I will handle by having a different calculation at that level using SCOPE. But at the employee by week level the following calculation resolves it.
AS
AVG({TAIL(LASTPERIODS(12, [Current Teams].[Current Team Structure].CurrentMember), 12)}, IIF ([Measures].[Total Brokerage] = 0, NULL,
AVG({TAIL(LASTPERIODS(12, [Current Teams].[Current Team Structure].CurrentMember), 12)},
AVG({TAIL(LASTPERIODS(12, [Dates].[Fiscal Calendar].CurrentMember), 12)}
)