Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Buying cycles.

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

3 Replies

Re: Buying cycles.

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Buying cycles.

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

Re: Buying cycles.

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.


talk is cheap, supply exceeds demand
Community Browser