Intervalmatch with use of one date

[mr_novice]

Hi All!

Sorry for the bad subject. I have some events with start date, end date and status. Im using intervalmatch to link this with the calendar. This works as expected in my chart where I count the amount of events per month with a specific status. But, I also want to calculate how many events where given a specific status each month. That is, if an event where given the status "Available" in Dec 2016 (Start Date) I only want to include it in the Dec bucket so to say and not in every month until the end date.

Let me give you an example:

Equip ID	Start Date	End Date	Status
1	2016-12-01	2017-01-05	Available
2	2017-01-02	2017-01-15	Unavailable
3	2017-01-02	2017-01-15	Available

I want a table and a date histogram per month like this:

1. YearMonth = 2016-12 -> Count (Disctinct Equip ID) = 1 (Equip ID = 1 because this status was created in Dec 2016)
2. YearMonth = 2017-01 -> Count (Disctinct Equip ID) = 1 (Equip ID = 3, Equip ID =1 is not included here because start date is in Dec 2016)

Any suggestions how I can accomplish this?

Br

Cris

[Gysbert_Wassenaar]

Perhaps you can solve it this way:

MyData:

LOAD

     [Equip ID],

     [Start Date],

     [End Date],

     MonthStart([Start Date],IterNo()-1) as Calendar.Month,

     If(IterNo()=1, 1, 0) as _flagStatusChanged

FROM

     ...source table...

WHILE

     MonthStart([Start Date],IterNo()-1) <= [End Date]

     ;

You can now use the _flagStatusChanged field that indicates if the status changed in a month:

count({<_flagStatusChanged={1},Status={'Available'}>} Distinct [Equip ID])

[mr_novice]

Hi gwassenaar‌

Thanks for the help (and sorry for late reply) but wouldnt this create a row per day per status? I want to avoid this. I did a separate script that grouped the data correctly and concatenated it with my fact table. Now I have two fact tables with different granularity - no intervalmatch used. Not ideal but it works.