Hi,

I have a sales cube with customer, product, order date and last order
date. I am trying to determine New, existing and old customers. I
also have a dimension called Type which only contains one member called
All Types.

Both date dimension go down to day level. What I want to do is get the
difference between the last order date and the current order date
selected by the user. I have tried using a datediff to get the
difference between the two date members which works.

The problem I have is that the last order date changes based on the
order date dimension I select.
e.g
order date, last order date
1/1/06,1/1/06
5/2/06,1/1/06
10/2/06,5/2/06

So to get the correct datediff value I want to first get the correct
last order date based on the order date i select so if the user
selected 10/2/06 then I want to automatically pick up 5/2/06 from the
last order date dimension for the datediff calculation.

Once that is done, I would then go onto work out New (customers 1 month
old), existing (customer who bought stuff >1month), old (customer
bought stuff >6 months old).

I could have done this in the fact table but it would mean that i would
need to store all combinations. I would like to make it dynamic so
based on what order date the users selects the that customer could be
New, Existing and Old.

I guess the problem is in two parts. First create a calculated member
that always get the correct last order date automatically based on the
order date dimension selection and then do a datediff. Second part
would be to use the datediff to determine New, Existing and Old
groupings.

Any help on this problem is much appreciated.

Thanks in advance.

Could you clarify the relationship between "order date" and "last order
date" - what do they represent? In your example, the order date of
01/01/2006 also has a last order date of 01/01/2006. But the order date
of 05/02/2006 still has a last order date of 01/01/2006, even though
05/02/2006 is listed as a last order date in the next line.

Also, are you using AS 2000 or AS 2005?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Order date is when an order is placed and last order date is when that
customer made the last purchase.

If a new order occurs then I use the first order date as my last order
date.
If a second order occurs I then go onto get the last order date from
previous line.
This ensures that there is a date in the last order date field.

I am using AS2000.

I have worked out most of the MDX but the problem I now have is on the
datediff function. This is the MDX I use to work out difference in
time

Measure Datecompare: -

datediff("m",[Measures].[datestring],[Order
Date].currentmember.properties("Firstday"))

The problem is that if datestring measure is null I get an #ERR in a
cell. This then stops the following calculated member from being
evaluated (Get #Err in all cells)

COUNT(Filter(crossjoin(DESCENDANTS([Customer].CURRENTMEMBER,[Customer].[Customerid]),{[Type].[Custtype].&[1]}),[Measures].[datecompare]=0))

If I try to use the IIF around the datediff it does not like it for
some reason.

Are there any alternatives so I can check for empty cells when using
datediff

Thanks






Deepak Puri wrote:

Hi Deepak,

I am figured out that CoalesceEmpty works when checking for empty
cells.

Next problem is that I have a very large customer dimension with over
1mill customers and will grow over time.

The following query checks at leaf level (customerid) and counts up
where the value is 0. This query runs in excessive of 15 mins.
Is there a way I can rewrite this in a better way to speed it up.

COUNT(Filter(crossjoin(DESCENDANTS([Customer].CURRENTMEMBER,[Customer].[Customerid]),{[Type].[Custtype].&[1]}),[Measures].[datecompare]=0))

Thanks


kish wrote:

Hi Deepak,

I am figured out that CoalesceEmpty works when checking for empty
cells.

Next problem is that I have a very large customer dimension with over
1mill customers and will grow over time.

The following query checks at leaf level (customerid) and counts up
where the value is 0. This query runs in excessive of 15 mins.
Is there a way I can rewrite this in a better way to speed it up.

COUNT(Filter(crossjoin(DESCENDANTS([Customer].CURRENTMEMBER,[Customer].[Customerid]),{[Type].[Custtype].&[1]}),[Measures].[datecompare]=0))

Thanks


kish wrote: