Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Counting the number of occurrences in between dates

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:

IDREGIONProductLaunch DATESTART DATEEND DATE
1Apineapple10/02/201510/02/201509/02/2020
2Bpineapple10/02/201610/02/2016

09/02/2022

3Cpineapple12/02/201812/02/2018

11/02/2020

4Aapple10/02/201310/02/201309/02/2017
5Bapple15/05/201615/05/201614/05/2023
6Capple16/05/201616/05/201615/05/2017
7Apineapple05/05/2015
8Bapple25/02/2018
9CMango22/05/2017
10AMango26/05/2020

TABLE_OUT:

REGIONYEARINSIDE_COUNTALL_COUNT
A201523
A201623
A201723
A201813
A201913
A202014
A202104
A202204
B201500
B201622
B201722
B201823
B201923
B202023
B202123
B202223
C201500
C201611
C201712
C201813
C201913
C

2020

13
C202103
C202203

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

Thank you very much!

1 Solution

Accepted Solutions
MVP

Check INSIDE_COUNT

UPDATE:

Table:

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

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

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

REGION,

Product,

[Launch DATE],

[START DATE],

[END DATE]

FROM

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

6 Replies
MVP

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
Not applicable
Author

Hi Sunny,

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

MVP

Check INSIDE_COUNT

UPDATE:

Table:

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

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

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

REGION,

Product,

[Launch DATE],

[START DATE],

[END DATE]

FROM

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

Anonymous
Not applicable
Author

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!

MVP

Do we still have the ALL_COUNT to fix, right?

Anonymous
Not applicable
Author

Sunny,

See OUTSIDE_IN attached.

Community Browser