MS SQL Server OLAP - Cube definition/MDX Query help needed

Author

Cube definition/MDX Query help needed

landlyst@gmail.com

2005-09-28, 7:24 am

Hi all

I am new to MDX and I have a problem getting the wanted output.

Here is the deal.

The dataset looks like this (Simplified)

Customer Group, Customer, Week, AccumSales

The Output I want is this:

Accum Sales
Customer Group 1 6500 (Sum of Last of Weeks)
Customer 1 3000 (Last of Weeks)
Week 1 1000
Week 2 2000
Week 3 3000
Customer 2 3500 (Last of Weeks)
Week 1 1500
Week 2 2500
Week 3 3500

The trick here is to sum up values in AccumSales but only if Week is
the last one within the customer.

How do I do this?

I know it is a bit weird to have AccumSales in a field in the dataset,
but this is how it is - Please do not ask me to change it :-)

Thanks

Bent

Darren Gosbell

2005-09-29, 3:24 am

The calculated measure below should do the trick for you.

It looks for the last non-empty time period and returns that. I'm not
sure if we can get the exact output you are after without a bit of
fiddling around.

I don't know what the structure of your time dimension is, but you will
note in the FoodMart sample I have used the EXCEPT function to take out
the monthly figures from the State Level. You might need to do a similar
thing to exclude the weeks from the Customer Group level

I think your query would look something like the following:
====================
===

WITH

MEMBER Measures.LastSales as 'SUM(TAIL(NONEMPTYCR
OSSJOIN(Descendants
(Time. CurrentMember,,LEAVE
S),{Measures.& #91;AccumSales]}),1)
)'

SELECT
{measures.[LastSales]} ON COLUMNS,
& #123;Crossjoin(Custo
mers.Members,Time.Month.Members) ON ROWS
FROM [Sales]

This is a sample of the Technique that works on FoodMart
====================
===
WITH

MEMBER Measures.x as 'SUM(TAIL(NONEMPTYCR
OSSJOIN(Descendants
(Time. CurrentMember,,LEAVE
S),{Measures.[Unit Sales]}),1))'

SET TIME_PERIODS as 'HIERARCHIZE({Time.year.& #91;1997],Descendant
s
(Time.Year.[1997],Time.month)})'

SELECT
{measures.[Unit Sales],measures.x} ON COLUMNS,
EXCEPT(nonemptycross
join(descendants(Cus
tomers.[State Province].
[OR],customers. city,SELF_AND_BEFORE
), TIME_PERIODS),{Crossjoin
({Customers.[State Province].[OR]},{Time.Month.Members})}) ON ROWS,
FROM SALES

====================
===

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127899284.640701.193440@g14g2000cwa.googlegroups.com>,
landlyst@gmail.com says...
> Hi all
>
> I am new to MDX and I have a problem getting the wanted output.
>
> Here is the deal.
>
> The dataset looks like this (Simplified)
>
> Customer Group, Customer, Week, AccumSales
>
>
> The Output I want is this:
>
> Accum Sales
> Customer Group 1 6500 (Sum of Last of Weeks)
> Customer 1 3000 (Last of Weeks)
> Week 1 1000
> Week 2 2000
> Week 3 3000
> Customer 2 3500 (Last of Weeks)
> Week 1 1500
> Week 2 2500
> Week 3 3500
>
> The trick here is to sum up values in AccumSales but only if Week is
> the last one within the customer.
>
> How do I do this?
>
> I know it is a bit weird to have AccumSales in a field in the dataset,
> but this is how it is - Please do not ask me to change it :-)
>
>
> Thanks
>
>
> Bent
>
>

Sponsored Links