Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_ULG
Creator
Creator

Identify date of previous transaction prior to defined date

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 IDTransaction IDSales Date
48274643788493513/04/2010
48274648865442225/06/2010
48274648936274328/06/2010
48274663875433223/08/2018
48274681256433305/10/2019
48274681946372412/10/2019
48274683882374717/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

Labels (2)
1 Solution

Accepted Solutions
Qlik_ULG
Creator
Creator
Author

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.

View solution in original post

5 Replies
zhadrakas
Specialist II
Specialist II

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
Creator
Creator
Author

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Qlik_ULG
Creator
Creator
Author

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
Creator
Creator
Author

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.