Relative last purchase date vs relative date

[NormanStanleyBadger]

I hope everybody is well.

Does anybody know of a way of solving this problem, please? 

We allocate each of our customers a status based on the time lapse between their last purchase and the current date. For example, if their last purchase date was in the last 2 months relative to today they would be active, last 3 to 4 months relative to today inactive etc

This is relatively straight forward to do on a one time basis, but I would also like to calculate it over time (on a daily basis) and be able to chart it and calculate averages.

To do this, I would need a way of calculating the last purchase date and date relative to each other.

For example,

1st March = Active customers total relative to the 1st March and purchase history up to that date

2nd March = Active customers total relative to the 2nd March and purchase history up to that date

Thanks in advance

[steeefan]

In your script, you could join the table containing the customer information incl. the last purchase date with a calendar table. That would give you an entry for each customer on each day that is contained in the calendar. For each entry, you could then calculcate the difference between last purchase date and the date coming from the calendar. If that difference were negative, the entry could be discarded. If it's positive, it can be classified according to your logic.

Depending on the size of your tables, this new table might have lots of rows. You could also do this dynamically on the GUI using set analysis but that might take quite a while to calculate if you change filters.

[NormanStanleyBadger]

I do not think that would quite work for me as I need the last purchase date to be relative to the date and vice versa. So at the 1st March, I need the last purchase date relative to that date, even though their current last purchase date might be the 4th March. I want their status as at the 1st March, then 2nd, 3rd and so on. I believe I might be able to do with interval match, but open to all ideas.

[NormanStanleyBadger · Accepted solution]

I have found a way to do this with Interval Match and a lot of help from the QlikSense Cookbook - Peek () Exchange rate recipe. 

It does produce a lot of records, so it would be interesting to know if there is another way, but this will do for me for now. 

Also, a note for anyone that might use this technique, if you have customers/entities with matching intervals, a situation arises were you get more rows in the model than stated by the output which will mess with your calculations. To resolve this you need to use the below.

Load 

distinct

Cheers

NSB