Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (2)
Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: Identify date of previous transaction prior to defined date

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
Highlighted
Specialist II
Specialist II

Re: Identify date of previous transaction prior to defined date

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;
Highlighted
Creator
Creator

Re: Identify date of previous transaction prior to defined date

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.

 

Highlighted
Digital Support
Digital Support

Re: Identify date of previous transaction prior to defined date

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.
Highlighted
Creator
Creator

Re: Identify date of previous transaction prior to defined date

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.

Highlighted
Creator
Creator

Re: Identify date of previous transaction prior to defined date

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