Identify date of previous transaction prior to defined date

[Qlik_ULG]

Hello,

I have an issue related to dates that I am trying to resolve. Basically, I have a list of products and each sale of the product has a sales date for the transaction. I am carrying out analysis on new products sold.

A new product is defined in 2 ways:

- Any product whose first sales date is in the last 3 years.

- Any product whose first sales date for transactions within the last 3 years is greater than 5 years after the previous sales date for that product.

Identifying based on the first criteria is straightforward, through taking the Min(sales_date) and grouping by Product.

However, the second criteria has proven to be a challenge. I started by getting the first sales date of all products, based on sales dates in the last 3 years only. I then need to be able to get the previous sales date related to the last transaction prior to this for the given product, which obviously won't be in the last 3 years.

As of yet, I have been unable to return this date using various functions.

Once I have that date, I'll simply be measuring the gap between both, to define whether the product counts as new or not.

This is all being done in the load script.

For example, the product below has 3 transactions. The first sales date is 13/04/2010. It's first sales date within the last 3 years is 23/08/2018. The previous sales date to this is 28/06/2010, so this would be categorised as a new product, given that the previous sales date is greater than 5 years before the first sales date within the last 3 years.

Product ID	Transaction ID	Sales Date
482746	437884935	13/04/2010
482746	488654422	25/06/2010
482746	489362743	28/06/2010
482746	638754332	23/08/2018
482746	812564333	05/10/2019
482746	819463724	12/10/2019
482746	838823747	17/12/2019

Any assistance with how to define the previous sales date (in this case 28/06/2010), would be greatly appreciated.

Thanks very much

[zhadrakas]

Maybe this:

RAW:
LOAD [Product ID], 
[Transaction ID], 
[Sales Date]
FROM YOUR_SAMPLE_DATA;

RAW_SORT:
NoConcatenate
LOAD *
Resident RAW
Order by [Product ID] ASC, [Sales Date] ASC;
drop table RAW;

FINAL:
NoConcatenate
LOAD [Product ID], 
[Transaction ID], 
[Sales Date],
if([Product ID] = peek([Product ID]) , previous([Sales Date])) as LAST_SALES_DATE 
Resident RAW_SORT
;

drop table RAW_SORT;

[Qlik_ULG]

Thank you very much for the help.

Your solution does provide the previous Sales Date to each transaction. However, I have been unable to structure it to only return the previous sales date to the 'First Sales Date within the last 3 years'.

This is simply created by taking the minimum Sales Date where the within the last 3 years and grouping by Product ID and is joined into the same table, so I essentially have:

[Product ID],

[Transaction ID],

[Sales Date],

[First Sales Date Last 3 Years]

It is the Sales Date prior to this final date that I'm struggling to retrieve.

I have tried to add where [Sales Date]=[First Sales Date Last 3 Years] to your last table, but this didn't return a value. Is there a modification i can apply that would return the previous Sales Date to this?

Thanks again.

[Brett_Bleess]

You may want to consider posting what you have in the QVW or post a sample such that folks can see the data model etc., this is generally what it takes on these more complex posts to get further potential solutions.  There is too much left to the imagination without it...

Regards,
Brett

[Qlik_ULG]

Thanks, Brett.

I have attached a sample QVW. I have commented where the solution above has been applied in the load script. This does return the previous sales date to each transaction.

However, what I am trying to return is the previous sales date to the [First Sales Date Last 3 Years]. I hope this helps to clarify the request.

Thanks again.

[Qlik_ULG · Accepted solution]

Having thought about this in more detail, I realise there is a more straightforward solution to the issue I outlined.

As I'm simply looking for the transaction and date immediately prior to the first within the last years, I simply need to pull the max from the period prior to the last 3 years. This provides what I need.

Thanks again for the assistance.