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].CurrentMember)))).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 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].CurrentMember)))).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].CurrentMember)))).Item(0).Item(0).uniquename '
...the [Time].[Week].[Date].members is replaced by a set of members that
looks something like ([time].[week].(All):[Time].[week].Currentmember) 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 members that
> looks something like ([time].[week].(All):[Time].[week].Currentmember) 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].Currentmember
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].CurrentMember)))).Item(0).Item(0).uniquename '
> ..the [Time].[Week].[Date].members is replaced by a set of members that
> looks something like ([time].[week].(All):[Time].[week].Currentmember) 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].CurrentMember)))).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.currentmembe r} 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

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

readxml doesnt work :(

hello!
i'm working on a project for pocket pc, which is an app that registers customers, suppliers, stocks and orders. all this is stored in the ppc itself using the SQL CE server.
i'm programming with VS2005, which is great, the tableadapters make the work lot easier.
i'm just having some problems importing and exporting data from the sql tables to files.
the export function works quiet well, i just load the data from the tableadapter into a table and then use the WriteXml(file) function.
Now, for importing I do more or less the same thing:
DataSetrTableAdapters.Customers cta = new ... etc etc
DataSetr.CustomersDataTable cdt = new DataSetr.CustomersDataTable();
cdt.ReadXml("\\My Documents\\customers.xml");
cta.Update(cdt);
result: empty table! :(
i also tried adding cta.Fill(cdt) before the update, but the result is the same....
any suggestions?
thank you very much

Hi,

The problem is that WriteXml writes the entire state of the dataset, including row state (added, modified, unchanged, etc.). When you call Fill, you add all rows to the DataSet from the table and Fill will automatically call AcceptChanges to mark all the rows as unmodified. So when you read it back from the file and call Update nothing happens - that's because the data-adapter doesn't see any row that needs processing.

I'm not sure what you're trying to achieve when reading from a file. Do you expect that those rows are *added* to the database, or you expect something more like a "merge" operation between the contents of the file and the contents of the database?

If you want to add the rows, then you have to options:

1) Avoid calling AcceptChanges on Fill. There is a property in the adapter called AcceptChangesDuringFill that you can use for that.

2) Alternatively, after loading the file into a DataSet, you can iterate over the rows and call SetAdded() on each row.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||i've been searching for this problem , but every page i read its about datasets.
i'n not using datasets, but datatables. the readxml is in the datatable class, not dataset. did you notice?
i want to import data to the sql server, and wipe out everything that is there. so i'll probably clean all the tables first, if necessary. after that i read from the xml and save everything again to the tables.

|||

Hi,

Actually, everything I mentioned applies to DataTable as well, including both approaches to ensure that the rows are marked as added.

If your goals is to wipe the table in the database and import the contents of the file, then you can simply first clean up the table and then use any of the options I mentioned in the previous post.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||

I have the same problem.

I have a dataset, I want to fill one table in the dataset using the xml data and other table is filled by the adapter from DB. Actually the DataTable.ReadXml(filename) is not working. Am I missing anything in the XML data file. Pablo, Can you give me an example. Thanks.

Steven

readxml doesnt work :(

hello!
i'm working on a project for pocket pc, which is an app that registers customers, suppliers, stocks and orders. all this is stored in the ppc itself using the SQL CE server.
i'm programming with VS2005, which is great, the tableadapters make the work lot easier.
i'm just having some problems importing and exporting data from the sql tables to files.
the export function works quiet well, i just load the data from the tableadapter into a table and then use the WriteXml(file) function.
Now, for importing I do more or less the same thing:
DataSetrTableAdapters.Customers cta = new ... etc etc
DataSetr.CustomersDataTable cdt = new DataSetr.CustomersDataTable();
cdt.ReadXml("\\My Documents\\customers.xml");
cta.Update(cdt);
result: empty table! :(
i also tried adding cta.Fill(cdt) before the update, but the result is the same....
any suggestions?
thank you very much

Hi,

The problem is that WriteXml writes the entire state of the dataset, including row state (added, modified, unchanged, etc.). When you call Fill, you add all rows to the DataSet from the table and Fill will automatically call AcceptChanges to mark all the rows as unmodified. So when you read it back from the file and call Update nothing happens - that's because the data-adapter doesn't see any row that needs processing.

I'm not sure what you're trying to achieve when reading from a file. Do you expect that those rows are *added* to the database, or you expect something more like a "merge" operation between the contents of the file and the contents of the database?

If you want to add the rows, then you have to options:

1) Avoid calling AcceptChanges on Fill. There is a property in the adapter called AcceptChangesDuringFill that you can use for that.

2) Alternatively, after loading the file into a DataSet, you can iterate over the rows and call SetAdded() on each row.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||i've been searching for this problem , but every page i read its about datasets.
i'n not using datasets, but datatables. the readxml is in the datatable class, not dataset. did you notice?
i want to import data to the sql server, and wipe out everything that is there. so i'll probably clean all the tables first, if necessary. after that i read from the xml and save everything again to the tables.

|||

Hi,

Actually, everything I mentioned applies to DataTable as well, including both approaches to ensure that the rows are marked as added.

If your goals is to wipe the table in the database and import the contents of the file, then you can simply first clean up the table and then use any of the options I mentioned in the previous post.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation

|||

I have the same problem.

I have a dataset, I want to fill one table in the dataset using the xml data and other table is filled by the adapter from DB. Actually the DataTable.ReadXml(filename) is not working. Am I missing anything in the XML data file. Pablo, Can you give me an example. Thanks.

Steven