Hi,

I need help creating the following mdx calculations. All are related to
the week dimension and the qty measure.

[Count Weeks On Sale] min (week) where qty > 0
[Last sale Week] max(week) where qty > 0
[% of Stores Selling] count( where qty > 0) /store_cnt


The time dimension has
Year
quarter
month
week

The most detailed level is product, week.

Any help with the expressions will be appreciated.

Asim.

Hi Asim,

Does the fact table contain sales records (so that qty = 0 is equivalent
to no fact records), and are you using AS 2005?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hey Deepak,

Yes I am using AS2005
Yes qty = 0 is equivalent to no records

I'll appreciate if you can get me started on these calculations.

Asim.



Deepak Puri wrote:

[Count Weeks On Sale] could be something like:

Count(NonEmpty([Time].[Week].[Week].Members,
{[Measures].[Qty]}))


Similarly, [Last sale Week] could be like:

Tail(NonEmpty([Time].[Week].[Week].Members,
{[Measures].[Qty]}).Item(0).Item(0).Name


But I'm not sure about [% of Stores Selling], until I know more about
the [Store] dimension - if there is one ...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks, I'll give this a go.

There is no third store dimension for this formula.
Only two dims are involved, the time and the sales.

Store_cnt is a simple attribute at the lowest level in the sales
heirarchy as is Qty.

Asim.


Deepak Puri wrote:

Thanks, I'll give this a go.

There is no third store dimension for this formula.
Only two dims are involved, the time and the sales.

Store_cnt is a simple attribute at the lowest level in the sales
heirarchy as is Qty.

Asim.


Deepak Puri wrote:

Actually I meant there is only 1 Time Dim and one sales Fact involved
for these calcs.
Store_cnt and Qty are both measures.

Asim.


Asim wrote:

Well, then I'm unclear about what the fact table, and the measure
"Store_Count" in particular, represent. Is each row just a periodic
snapshot summary of sales activity, so that there could be rows with Qty
= 0?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Yes, Qty could be null which means it didn't exist for that product and
week or it could be 0 meaning there were no sales for that product that
week. If Qty is 0 or null, store_cnt is 0 else store_cnt is at least
one.

Asim.

Deepak Puri wrote:

Asim, based on the info so far, I'm still not clear how [% of Stores
Selling] is defined - can you explain it in more detail? And since there
isn't a Store dimension, is store_count a Distinct Count measure on some
StoreID field - if not, I'm not sure how store analysis could be done.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***