MDX - Avg Per Store

[justins57]

Is it possible to iterate through a level above store to get the number of stores, aggregate for stores, and the per store averages for several measures shown in the columns? Example MDX below.

WITH
SET OrgSet as 'Descendants([Store].[All Stores].[USA], [Store].[Store State])'
SET RowSet As 'Generate(OrgSet,{Store.CurrentMember, [Store].[Per Store Avg]},ALL)'
SET ColSet As 'Measures.Members'
MEMBER [Measures].[# Stores] As 'IIF(Store.CurrentMember IS Store.[Per Store Avg]," ", Cstr(Count(Descendants(Store.CurrentMember,[Store].[Store Name]))))'
MEMBER [Store].[Per Store Avg] As 'Avg(Descendants(Store.CurrentMember,Store.[Store Name]))'
SELECT
{Measures.[# Stores], ColSet} on Columns,
RowSet on Rows
FROM Sales
WHERE ([Product].[All Products], [Date].[Fiscal].[All Date].[1997].[12])

 

[justins57]

I've been told by an MDX guru that I can create a Calculation Dimension (by building a single row dimension table, add the dimension key to the fact table, create the dimension in Analysis Manager, and link the dimension to cube. Afterwards, I should be able to use the Calc Dimension for creating the calculated member "Per Store Avg".

I'm still not able to get this to work. Any suggestions or solutions are appreciated.

Thanks,

Justin

 

[MDXer]

The process you are talking about is an artifact cube. I have been toying with the idea of writing a FAQ about how to do it but haven't had the time. so I guess I'll make the time. Will probably take a couple days to write up but I'll get it done.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[justins57]

Any progress on the FAQ for this artifact cube? Thanks for your time and effort.

Justin

 

[MyJash21]

Hi,

I MDX query return the result as follows;

Week Stock Sold
1 500 100
2 350 50
3 300 50

But I wanted to have the result as;

Week Stock Sold % Sold (Calculated field)
1 500 100 Null
2 350 50 50/500 = 10%
3 300 50 50/300 = 16.6 %


Note:

“% sold” field of each week should be calculated as “Last week Stock / This week sales”

How can I achieve this, please advise.

Advanced thanks.

 

[justins57]

MJ,

You should have started a new thread for this query.

I think I understand your query.

To get the results you want you have to have a Week Hierarchy or a Week Level for your Date Dimension. Once you have this, you can easily get %Sold by using the following MDX (FoodMart 2000):

WITH
MEMBER [Measures].[% Sold] As '([Time].CurrentMember.PrevMember, [Measures].[Sold])/[Measures].[Stock]',format_string='Percent'
MEMBER [Measures].[Stock] As '[Measures].[Unit Sales]',format_string='#,#'
MEMBER [Measures].[Sold] As '[Measures].[Sales Count]',format_string='#,#'
MEMBER [Measures].[PrevMbr] As '[Time].CurrentMember.PrevMember.Name'
SET ColSet As '{[Measures].[Stock], [Measures].[Sold], [Measures].[% Sold],[Measures].[PrevMbr]}'
SET RowSet As '{[Time].[Month].Members, [Time].[Quarter].Members, [Time].[Year].Members}'
SELECT
ColSet on Columns,
RowSet on Rows
From Sales

hth,

Justin

 

[MyJash21]

Hi Justin,

Thanks for your prompt response. Let me try your suggestions.

 

[MyJash21]

Hi,

The MDX query given below returns result for all years. i just want it to return for a specific year (let's say 2005), how can i achieve this? please advise


WITH
MEMBER [Measures].[% Sold] As '([Fiscal Year].CurrentMember.PrevMember, [Measures].[Sold])/[Measures].[Stock]',format_string='Percent'
MEMBER [Measures].[Stock] As '[Measures].[Retailer SoH]',format_string='#,#'
MEMBER [Measures].[Sold] As '[Measures].[Sellthru Units]',format_string='#,#'
MEMBER [Measures].[PrevMbr] As '[Fiscal Year].CurrentMember.PrevMember.Name'
SET ColSet As '{[Measures].[Stock], [Measures].[Sold], [Measures].[% Sold],[Measures].[PrevMbr]}'
SET RowSet As '{[Fiscal Year].[Fiscal Week End Dt].Members}'
SELECT
ColSet on Columns,
RowSet on Rows
From Master

 

[justins57]

Hi,

Add a where clause without the quotes "WHERE([Fiscal Year].[Year].[2005])" after the FROM clause. This should slice the data to only include 2005.

hth,

justin