/*-- Aggregate leaf data -----------------------------------------------*/
Calculate;
/*-----------------------------------------------------------------------------
| Internet Sales Calculations |
-----------------------------------------------------------------------------*/
Create Member CurrentCube.[Measures].[Internet Gross Profit]
As [Measures].[Internet Sales Amount]
-
[Measures].[Internet Total Product Cost],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior =
{
[Internet Sales Amount],
[Internet Total Product Cost]
};
Create Member CurrentCube.[Measures].[Internet Gross Profit Margin]
As (
[Measures].[Internet Sales Amount]
-
[Measures].[Internet Total Product Cost]
)
/
[Measures].[Internet Sales Amount],
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior =
{
[Internet Sales Amount],
[Internet Total Product Cost]
};
Create Member CurrentCube.[Measures].[Internet Average Unit Price]
As [Measures].[Internet Unit Price]
/
[Measures].[Internet Transaction Count],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Unit Price];
Create Member CurrentCube.[Measures].[Internet Average Sales Amount]
As [Measures].[Internet Sales Amount]
/
[Measures].[Internet Order Count],
Format_String = "Currency",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount];
Create Member CurrentCube.[Measures].[Internet Ratio to All Products]
As [Measures].[Internet Sales Amount]
/
(
Root( [Product] ),
[Measures].[Internet Sales Amount]
),
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales',
Non_Empty_Behavior = [Internet Sales Amount];
Create Member CurrentCube.[Measures].[Internet Ratio to Parent Product]
As Case
When [Product].[Product Categories].CurrentMember.Level.Ordinal
= 0
Then 1
Else [Measures].[Internet Sales Amount]
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales';
Create Member CurrentCube.[Measures].[Growth in Customer Base]
As Case
When [Date].[Fiscal].CurrentMember.Level.Ordinal = 0
Then "NA"
When IsEmpty
(
(
[Date].[Fiscal].CurrentMember.PrevMember,
[Measures].[Customer Count]
)
)
Then Null
Else (
( [Date].[Fiscal].CurrentMember, [Measures].[Customer Count] )
-
( [Date].[Fiscal].PrevMember, [Measures].[Customer Count] )
)
/
( [Date].[Fiscal].PrevMember,[Measures].[Customer Count] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Internet Sales';
/*-----------------------------------------------------------------------------
| Reseller Sales Calculations |
-----------------------------------------------------------------------------*/
Create Member CurrentCube.[Measures].[Reseller Gross Profit]
As [Measures].[Reseller Sales Amount]
-
[Measures].[Reseller Total Product Cost],
Format_String = "Currency",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior =
{
[Reseller Sales Amount],
[Reseller Total Product Cost]
};
Create Member CurrentCube.[Measures].[Reseller Gross Profit Margin]
As (
[Measures].[Reseller Sales Amount]
-
[Measures].[Reseller Total Product Cost]
)
/
[Measures].[Reseller Sales Amount],
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior =
{
[Reseller Sales Amount],
[Reseller Total Product Cost]
};
Create Member CurrentCube.[Measures].[Reseller Average Unit Price]
As [Measures].[Reseller Unit Price]
/
[Measures].[Reseller Transaction Count],
Format_String = "Currency",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Reseller Unit Price];
Create Member CurrentCube.[Measures].[Reseller Average Sales Amount]
As [Measures].[Reseller Sales Amount]
/
[Measures].[Reseller Order Count],
Format_String = "Currency",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Reseller Sales Amount];
Create Member CurrentCube.[Measures].[Reseller Ratio to All Products]
As [Measures].[Reseller Sales Amount]
/
(
Root( [Product] ),
[Measures].[Reseller Sales Amount]
),
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Reseller Sales Amount];
Create Member CurrentCube.[Measures].[Reseller Ratio to Parent Product]
As Case
When [Product].[Product Categories].CurrentMember.Level.Ordinal
= 0
Then 1
Else [Measures].[Reseller Sales Amount]
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Reseller Sales Amount] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales';
Create Member CurrentCube.[Measures].[Discount Percentage]
As [Measures].[Discount Amount]
/
[Measures].[Reseller Sales Amount],
Format_String = "Percent",
Associated_Measure_Group = 'Reseller Sales',
Non_Empty_Behavior = [Discount Amount];
/*-----------------------------------------------------------------------------
| Sales Summary Calculations |
-----------------------------------------------------------------------------*/
Create Member CurrentCube.[Measures].[Average Unit Price]
As [Measures].[Unit Price]
/
[Measures].[Transaction Count],
Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',
Non_Empty_Behavior = [Unit Price];
Create Member CurrentCube.[Measures].[Average Sales Amount]
As [Measures].[Sales Amount]
/
[Measures].[Order Count],
Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',
Non_Empty_Behavior = [Sales Amount];
Create Set CurrentCube.[Long Lead Products]
As Exists
(
[Product].[Model Name].[Model Name].Members,
[Product].[Days to Manufacture].&[4]
)
,
Display_Folder = 'Sets';
Create Set CurrentCube.[Core Product Group]
As DrillDownMember
(
[Product].[Product Categories].[Category].Members,
[Product].[Product Categories].[Category].[Bikes]
)
,
Display_Folder = 'Sets';
Create Set CurrentCube.[Large Resellers]
As Exists
(
[Reseller].[Reseller].[Reseller].Members,
[Reseller].[Number of Employees].[Number of Employees].[81 - 100]
)
,
Display_Folder = 'Sets';
Create Set CurrentCube.[High Discount Promotions]
As Exists
(
[Promotion].[Promotion].[Promotion].Members,
Filter
(
[Promotion].[Discount Percent].[Discount Percent].Members,
[Promotion].[Discount Percent].CurrentMember.MemberValue >= .30
)
)
,
Display_Folder = 'Sets';
Create Dynamic Set CurrentCube.[Top 25 Selling Products]
As TopCount
(
[Product].[Product].[Product].Members,
25,
[Measures].[Sales Amount]
)
,
Display_Folder = 'Sets';
Create Dynamic Set CurrentCube.[Top 50 Customers]
As TopCount
(
[Customer].[Customer].[Customer].Members,
50,
[Measures].[Internet Sales Amount]
)
,
Display_Folder = 'Sets';
Create Dynamic Set CurrentCube.[Negative Margin Products]
As Filter
(
[Product].[Subcategory].[Subcategory],
[Measures].[Gross Profit Margin] < 0
)
,
Display_Folder = 'Sets';
Create Member CurrentCube.[Measures].[Gross Profit]
As [Measures].[Sales Amount]
-
[Measures].[Total Product Cost],
Format_String = "Currency",
Associated_Measure_Group = 'Sales Summary',
Non_Empty_Behavior =
{
[Measures].[Sales Amount],
[Measures].[Total Product Cost]
};
Create Member CurrentCube.[Measures].[Gross Profit Margin]
As (
[Measures].[Sales Amount]
-
[Measures].[Total Product Cost]
)
/
[Measures].[Sales Amount],
Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary',
Non_Empty_Behavior =
{
[Measures].[Sales Amount],
[Measures].[Total Product Cost]
};
Create Member CurrentCube.[Measures].[Ratio to All Products]
As [Measures].[Sales Amount]
/
(
Root( [Product] ),
[Measures].[Sales Amount]
),
Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary',
Non_Empty_Behavior = { [Sales Amount] };
Create Member CurrentCube.[Measures].[Ratio to Parent Product]
As Case
When [Product].[Product Categories].CurrentMember.Level.Ordinal
= 0
Then 1
Else [Measures].[Sales Amount]
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Sales Amount] )
End,
Format_String = "Percent",
Associated_Measure_Group = 'Sales Summary';
/*-----------------------------------------------------------------------------
| Sales Quota Allocation |
-----------------------------------------------------------------------------*/
/*-- Allocate equally to quarters in H2 FY 2013 ------------------------*/
Scope
(
[Date].[Fiscal Year].&[2013],
[Date].[Fiscal].[Fiscal Quarter].Members,
[Measures].[Sales Amount Quota]
);
This = ParallelPeriod
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember
) * 1.35;
/*-- Allocate equally to months in FY 2011 -----------------------------*/
Scope
(
[Date].[Fiscal Year].&[2011],
[Date].[Fiscal].[Month].Members
);
This = [Date].[Fiscal].CurrentMember.Parent / 3;
End Scope;
/*-- Weighted allocation to remaining months ---------------------------*/
// Pin quarterly values prior to assigning weights for months
// This is done in order to avoid overwriting the quarterly values
// once weights are entered for monthly values.
Freeze
(
[Date].[Fiscal].[Fiscal Quarter].Members,
[Measures].[Sales Amount Quota]
);
// Scope on month level in FY 2012 and onwards
Scope
(
[Date].[Fiscal Year].&[2012] : Null,
[Date].[Fiscal].[Month].Members
);
// Compute weights based on reseller sales ratio in previous year
This =
(
ParallelPeriod // Fetch reseller sales amount in previous year
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember
),
[Measures].[Reseller Sales Amount]
)
/
(
ParallelPeriod // Divide monthly value by quarterly value to obtain ratio
(
[Date].[Fiscal].[Fiscal Year], 1,
[Date].[Fiscal].CurrentMember.Parent
),
[Measures].[Reseller Sales Amount]
);
// Allocate quarterly values to months according to weight
This = [Measures].CurrentMember * [Date].[Fiscal].Parent;
End Scope;
End Scope; |