topic Re: Identify date of previous transaction prior to defined date in QlikView

Identify date of previous transaction prior to defined date

Qlik_ULG — Sat, 16 Nov 2024 01:39:53 GMT

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

Re: Identify date of previous transaction prior to defined date

zhadrakas — Thu, 19 Dec 2019 08:50:11 GMT

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;

Re: Identify date of previous transaction prior to defined date

Qlik_ULG — Fri, 20 Dec 2019 16:05:45 GMT

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.

Re: Identify date of previous transaction prior to defined date

Brett_Bleess — Mon, 23 Dec 2019 20:45:34 GMT

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

Re: Identify date of previous transaction prior to defined date

Qlik_ULG — Fri, 27 Dec 2019 15:10:42 GMT

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.

Re: Identify date of previous transaction prior to defined date

Qlik_ULG — Fri, 03 Jan 2020 10:26:11 GMT

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.