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

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(NONEMPTYCROSSJOIN(Descendants
(Time.CurrentMember,,LEAVES),{Measures.[AccumSales]}),1))'

SELECT
{measures.[LastSales]} ON COLUMNS,
{Crossjoin(Customers.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(NONEMPTYCROSSJOIN(Descendants
(Time.CurrentMember,,LEAVES),{Measures.[Unit Sales]}),1))'

SET TIME_PERIODS as 'HIERARCHIZE({Time.year.[1997],Descendants
(Time.Year.[1997],Time.month)})'

SELECT
{measures.[Unit Sales],measures.x} ON COLUMNS,
EXCEPT(nonemptycrossjoin(descendants(Customers.[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_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1127899284.640701.193440 (AT) g14g2000cwa (DOT) googlegroups.com>,
landlyst (AT) gmail (DOT) com says...