Recency, time since last sale

Hi,
I'm trying to implement a recency measure for my individual customers,
telling me how many days have gone by since the last purchase of a customer.
I would like it to go something like this:
with member time.week.last_purchase_date as
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename '
member measures.days_since_last_purchase as
'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember)'
Of course there is no Datediff in MDX, but how can I construct the syntax to
meet my requirements?
Many thanks!have you tried using vba functions? DateDiff() and CDate() '
"Jacob_I" wrote:

> Hi,
> I'm trying to implement a recency measure for my individual customers,
> telling me how many days have gone by since the last purchase of a custome
r.
> I would like it to go something like this:
> with member time.week.last_purchase_date as
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].Cur
rentMember)))).Item(0).Item(0).uniquename '
> member measures.days_since_last_purchase as
> 'Datediff(time.week.lastpurchasedate : [Time].[Week].Currentmember
)'
> Of course there is no Datediff in MDX, but how can I construct the syntax
to
> meet my requirements?
> Many thanks!|||Hi Mike
Actually, I didn't think about that at all. I'll try that and post my
findings.
"mike" wrote:
[vbcol=seagreen]
> have you tried using vba functions? DateDiff() and CDate() '
> "Jacob_I" wrote:
>|||As far as the MDX goes, you could substract index positions of two dates.
It would mean that you find index position of last purchase date in the date
dimension and then find index position of 'today' or some other date...
MC
"Jacob_I" <JacobI@.discussions.microsoft.com> wrote in message
news:5C0C8187-C289-4E36-92BE-11F711D5B6E1@.microsoft.com...[vbcol=seagreen]
> Hi Mike
> Actually, I didn't think about that at all. I'll try that and post my
> findings.
> "mike" wrote:
>|||Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
thansk to you valuable advice. But I also need it to be time-sensitive, so
that when I use the calc member:
'Tail(Filter([Time].[Week].[Date].members, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename '
..the [Time].[Week].[Date].members is replaced by a set of memb
ers that
looks something like ([time].[week].(All):[Time].[week].Curr
entmember) ie. it
returns all members from beginning of time until time.currentmember.
Can this be done? Maybe it's woth a new thread?
Thanks in advance!
Regards, Jacob|||In article <E88FB2ED-46C5-4DDC-B4A6-C1222C611986@.microsoft.com>,
JacobI@.discussions.microsoft.com says...
> ..the [Time].[Week].[Date].members is replaced by a set of mem
bers that
> looks something like ([time].[week].(All):[Time].[week].Cu
rrentmember) ie. it
> returns all members from beginning of time until time.currentmember.
>
You could use something like
[time].[week].members.item(0):[Time].[week].Currentmember
Or, to be completely idependant of which level you are on:
[time].CurrentMember.Level.Members.item(0):[Time].[week].Current
member
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell|||hi Jacob,
I am also facing a similar problem with the datediff. Could you share on how
you overcame the prob on datediff.
Thanks in advance
Prady
"Jacob_I" wrote:

> Thanks a lot Mike & MC. I've gotten the datediff stuff working beautifully
> thansk to you valuable advice. But I also need it to be time-sensitive, so
> that when I use the calc member:
> 'Tail(Filter([Time].[Week].[Date].members, Not
> IsEmpty(([Measures].[Quantity],[Customers].[Geography].Cur
rentMember)))).Item(0).Item(0).uniquename '
> ..the [Time].[Week].[Date].members is replaced by a set of mem
bers that
> looks something like ([time].[week].(All):[Time].[week].Cu
rrentmember) ie. it
> returns all members from beginning of time until time.currentmember.
> Can this be done? Maybe it's woth a new thread?
> Thanks in advance!
> Regards, Jacob
>
>|||Hi Prady,
Although I don't have the code right here, I can illustrate the layout of my
final query:
// This part gives me the date of last puchase and case it to DATE type
CDate(Tail(Filter({<Configure a date member range>}, Not
IsEmpty(([Measures].[Quantity],[Customers].[Geography].Curre
ntMember)))).Item(0).Item(0).uniquename) -
// Minus the currentdate
Now()
(end)
The date member range can be anything from a date range in a named set to
something that changes with the time.currentmember, eg.
{time.currentmember.lag(182):time.currentmember} which if a date-level
will
give you a range of half a year. As this query if quite heavy for at large
customer dimension it's rather sensible to have a small date member range.
The minus part can also be Cdate(time.currentmember.name) which (depending
on your data level member formatting) also will give you a DATE type result
to minus with the first part.
HTH
Jacob_I