Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anselmo_meira
Creator II
Creator II

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
sunny_talwar

Check INSIDE_COUNT

Capture.PNG

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

View solution in original post

6 Replies
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?

anselmo_meira
Creator II
Creator II
Author

Hi Sunny,

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

sunny_talwar

Check INSIDE_COUNT

Capture.PNG

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

View solution in original post

anselmo_meira
Creator II
Creator II
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!

sunny_talwar

Do we still have the ALL_COUNT to fix, right?

anselmo_meira
Creator II
Creator II
Author

Sunny,

See OUTSIDE_IN attached.