Count in a date dimension table grouped by date start and end

[SebastianRichter]

Hi everybody,

I need your help with the following issue. Many thanks in advance.

Here is an example of the table that I have as input (round about 650 rows):

Machine No.	Machine Type	Business Unit	Warranty Start	Warranty End
A	1	Alpha	01.04.2018	30.03.2019
B	2	Charlie	03.05.2020	02.05.2022
C	1	Alpha	02.09.2021	01.09.2022
D	4	Delta	19.01.2022	18.01.2024
E	3	Delta	20.09.2022	19.09.2023

Now I need to know, during which time period we had, have and will have how many machines covered by warranty.

I need to be able to filter this by Machine Type and Business Unit.

Finally it should look like this being able to look at least 36 monthes back and make a forecast for the oncoming 6 monthes:

DimDate	Count of machines in warranty		Filter 1 = Machine Type
01.04.2018	1	which is machine A	Filter 2 = Business Unit
02.04.2018	1	which is machine A
03.04.2018	1	which is machine A
(…)
01.01.2021	1	which is machines B
01.10.2021	2	which are machines B and C
(…)
01.01.2023	2	which are machines D and E
(…)
10.01.2024	2	which is machines D
(…)
01.01.2025 02.01.2025 03.01.2025	0 0 0

[rubenmarin · Accepted solution]

Hi, you can create a calendar that covers all dates from the min date to the max date of all warranties.

The use IntervalMach to assign each date to the ranges that include that date, and join all data until you have a table with all dates and the machine warranties included in each data.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

With that table you will only need to add date as dimension and make a count (and maybe a concat) of machine field. And bot filters will work.