Is a certain timespan between dates existing?

[AndreSchwarze]

Hello Qlik-Community,
I´m having a lot of invoice dates for certain products of the last 26 years. With ca. 80,000 products.
So there is invoice.product and invoice.createdate
Im looking for products where a span of at least x-Months between two invoices is existing.
Or paraphrased: Is there a gap of at least x-Months between twoe invoices, then show me invoice.product, otherwise NULL().

As an example:

Nr.		DaysBetween
1	01.01.2001
2	01.05.2001	120
3	01.01.2002	245
4	01.01.2005	1096
5	01.05.2006	485

You can clearly identify that between Nr. 3 and 4 there are 36 Months, and that is the gap we need to identify.

Does anyone know how to create the syntax for that?
Honestly I´m lost how to approach that issue.

Thank you in advance!

[Qrishna · Accepted solution]

I would suggest you doing this in ethe script rather on UI, even though we could still achive the same.

Try Below:

data:
load *,
if(MonthsElpased > 12, 1, 0) as x_MonthsElapsed_flag //more than 12 months elapsed flag
;
load *,
if(product = peek(product), createdate - peek(createdate)) as DaysElapsed,
if(product = peek(product), ((year(createdate)*12)+month(createdate)) - ((year(peek(createdate))*12)+month(peek(createdate)))) as MonthsElpased
;
load product,
product.id,
date#(createdate, 'DD.DD.YYYY') as createdate
inline [
product, product.id, createdate
A,1,01.01.2001
A,2,01.05.2001
A,3,01.01.2002
A,4,01.01.2005
A,5,01.05.2006

B,1,11.01.2001
B,2,14.04.2005
B,3,26.11.2011
B,4,05.09.2020
B,5,07.08.2024

];

Qualify *;
d:
Load product, product.id, createdate, MonthsElpased
resident data
where MonthsElpased >12;

[AndreSchwarze]

Hello Qrishna,
I had no chance to implement that in the script (loading via Datamarts), but was inspired by your post to recreate something with dimensions.
Thank you a lot for your efforts!

BR