Counting the number of occurrences in between dates

[Anonymous]

Hi all,

What I am trying to do is to count per REGION and per YEAR the number of products, where if the YEAR (from 2015 to 2023)  is in between the START DATE and END DATE of a product it counts it and put in INSIDE_COUNT. The other count, ALL_COUNT, counts the number accumulated per year of products launched. If it was launched 1 in 2015 and 2 in 2016, it counts 1 for 2015 and 3 for 2016. There is a table with data below and desirable output.

TABLE_IN:

ID	REGION	Product	Launch DATE	START DATE	END DATE
1	A	pineapple	10/02/2015	10/02/2015	09/02/2020
2	B	pineapple	10/02/2016	10/02/2016	09/02/2022
3	C	pineapple	12/02/2018	12/02/2018	11/02/2020
4	A	apple	10/02/2013	10/02/2013	09/02/2017
5	B	apple	15/05/2016	15/05/2016	14/05/2023
6	C	apple	16/05/2016	16/05/2016	15/05/2017
7	A	pineapple	05/05/2015
8	B	apple	25/02/2018
9	C	Mango	22/05/2017
10	A	Mango	26/05/2020

TABLE_OUT:

REGION	YEAR	INSIDE_COUNT	ALL_COUNT
A	2015	2	3
A	2016	2	3
A	2017	2	3
A	2018	1	3
A	2019	1	3
A	2020	1	4
A	2021	0	4
A	2022	0	4
B	2015	0	0
B	2016	2	2
B	2017	2	2
B	2018	2	3
B	2019	2	3
B	2020	2	3
B	2021	2	3
B	2022	2	3
C	2015	0	0
C	2016	1	1
C	2017	1	2
C	2018	1	3
C	2019	1	3
C	2020	1	3
C	2021	0	3
C	2022	0	3

Is the INSIDE_COUNT better done with intervalmatch or is there a better way of doing that?

Thank you very much!

[sunny_talwar]

I think IntervalMatch should be the way to do this. My only doubt above is regarding the Start and End Date in the same table as the rest of the data?

[Anonymous]

Hi Sunny,

Start and End is in the same table data. Also Launch Date.

[sunny_talwar · Accepted solution]

Check INSIDE_COUNT

UPDATE:

Added script:

Table:

LOAD *,

  Date([START DATE] + IterNo() - 1) as DATE,

  Year([START DATE] + IterNo() - 1) as YEAR

While [START DATE] + IterNo() - 1 <= [END DATE];

LOAD ID,

     REGION,

     Product,

     [Launch DATE],

     [START DATE],

     [END DATE]

FROM

[https://community.qlik.com/thread/225121]

(html, codepage is 1252, embedded labels, table is @1);

[Anonymous]

Mate,

I did it myself and replied it on the other post, but I should say that I have to learn qlikview a lot. Lol

Your code was less than 20 lines! That was great!

[sunny_talwar]

Do we still have the ALL_COUNT to fix, right?

[Anonymous]

Sunny,

See OUTSIDE_IN attached.