Buying cycles.

[#1]

Does anyone have some good examples of how to handle buying cycles.  I have a client that buys their first product in Jan. How long is it before they buy again and what product did they purchase. Amy ideas would be welcome.

Thanks

David

[Gysbert_Wassenaar]

Load the data ordered by client and then by purchase date. You can use the previous() function to get the previous purchase date so you can calculate the difference between the two dates. Something like this:

Temp:

Load client, date, product

From ...mysource...;

Result:

Load client, date, product,

    if(previous(client)=client, interval(rangesum(date, -previous(date)),'d')) as cycle_days,

    if(previous(client)=client, previous(product)) as previous_product

Resident Temp

order by client, date;

drop table Temp;

[#3]

I am still new to QlikView so I am not sure how to use the formula.  I understand the syntax.  I have tried your option but I am getting syn-key's.  I will need to play with it some more.

David

[Gysbert_Wassenaar]

The previous function retrieves a value from the previous record of the source table. It's used to check we're still dealing with the same client so we don't subtract dates from different clients. It's also used to get the date from the previous record so it can be subtracted from the date value of the current record.

The interval function is merely to format a date (or time) interval, in this case in days.

The rangesum function takes simply sums all the arguments passed to it. Null values are ignored, which is how it differs from using the + operator.

Did you include the drop table Temp line in your script? Not doing so would cause a synthetic key. It's also possible you loaded another table that has several fields in common with the Result table. In that case you'll have to decide if the tables are associated correctly. Synthetic keys are not per definition wrong, but they do often indicate a problem in the data model.