topic Counting the number of occurrences in between dates in QlikView

Counting the number of occurrences in between dates

Anonymous — Thu, 14 Jul 2016 14:02:12 GMT

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!

Re: Counting the number of occurrences in between dates

sunny_talwar — Thu, 14 Jul 2016 14:05:42 GMT

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?

Re: Counting the number of occurrences in between dates

Anonymous — Thu, 14 Jul 2016 14:16:49 GMT

Hi Sunny,

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

Re: Counting the number of occurrences in between dates

sunny_talwar — Thu, 14 Jul 2016 18:58:30 GMT

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);

Re: Counting the number of occurrences in between dates

Anonymous — Thu, 14 Jul 2016 19:27:36 GMT

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!

Re: Counting the number of occurrences in between dates

sunny_talwar — Thu, 14 Jul 2016 20:29:44 GMT

Do we still have the ALL_COUNT to fix, right?

Re: Counting the number of occurrences in between dates

Anonymous — Thu, 14 Jul 2016 21:23:41 GMT

Sunny,

See OUTSIDE_IN attached.