Count rows for each year in range it's included in

[rtlagot]

Hello everyone,

I've been looking for an answer to my problem and I can't seem to find any.

I have a Table with one column StartDate and a column EndDate. These two dates can be months to years appart. I'm looking to count the number of rows for each year it is included in.

For example, if StartDate is 03/03/2020 and EndDate is 04/06/2022, it should count this row for 2020, 2021 and 2022. The ultimate goal is to show this count in a bar graph with Year used as Dimension.

Thanks for your answers,

Rtlagot

[cristianj23a]

Hi Rtlagot.

This can be achieved using the IntervalMatch function in Qlik to associate the dates in the data rows with their corresponding years. Here is a sample script on how you can do this:

// Load the table with StartDate and EndDate
[Original_Table]:
LOAD * INLINE [
StartDate, EndDate
03/03/2020, 04/06/2022
01/01/2019, 03/03/2021
07/07/2021, 08/08/2023
];

// Generate a list of years between the min and max years in the dataset
// We need to extend this by 1 year to include the EndDate in the interval
LET vMinYear = Year(Min(StartDate));
LET vMaxYear = Year(Max(EndDate)) + 1;

[Years]:
LOAD
Year($(vMinYear) + IterNo() - 1) AS Year
AUTOGENERATE(1)
WHILE $(vMinYear) + IterNo() - 1 < $(vMaxYear);

// Use IntervalMatch to map the start and end dates to years
[Interval_Match_Table]:
INTERVALMATCH (Year)
LOAD
StartDate,
EndDate
RESIDENT Original_Table;

// Count the rows for each year
[Yearly_Counts]:
LOAD
Year,
Count(StartDate) AS Count
RESIDENT Interval_Match_Table
GROUP BY Year;

After this, you can use 'Year' as dimension and 'Count' as expression in your bar chart. Be sure to replace 'Original_Table', 'StartDate', and 'EndDate' with the actual names in your script.

Note that this script assumes that your date fields are recognized by Qlik as date fields. If they're not, you'll need to convert them to date using the Date#() and Date() functions.

Regarts.

[rtlagot]

Hi Cristian,

Thanks for your answer, I start to understand it a bit better. I have a question regarding your code, if I understand well, in the end you count only the number of StartDate for each year right ?

Best Regards,

[cristianj23a]

That's right, it counts as a year.
The formula is oriented so that you can filter the graphs by year, month or date and it will give you the subtraction as a result.

Regarts.

[rtlagot]

Thanks for your answer. It only solves part of my problem, as I'm looking to count a row not only for its starting year, but also for every year between its starting year and its end year. 

Best Regards,