Ideally i'd like to not have to have a separate table with all the intersecting period joined on periodID in it simply due to size (for example just the test example above gives 42 matched periods and there are currently over 92000 periods in the limited data i'm pulling)
However if there is no other way then i'll have to look at working out how to do that in script (would be easy in SQL but different data sources...) .
Apologies, have been away from my desk for a few days, yep that would be the ask, i'd done it for a given period rather than all periods of a given Facil_ID but due to set analysis only being calculated once per chart my brain fell down a little when trying to nest it so heading toward the idea of doing it in script somehow.
managed to get some time to look at this again today and did it in script with a duplicate of my main fact table and a table sitting in between to do the joining between the two as i think doing it with a complicated Set Analysis expression is probably just breaking Qlik Sense to fit the need too much
(the derived start and ends just fill in empty end dates with today's date where a given period is ongoing)
the code to generate the periodlink table is a bit inelegant but seems to work reasonably quickly:
TEST: LOAD ConversionID as CID1 ,DerivedStartDTTM as S1 ,DerivedEndDTTM as E1 ,Location as LocationTest
LEFT Join LOAD ConversionID as CID2 ,DerivedStartDTTM as S2 ,DerivedEndDTTM as E2 ,Location as LocationTest
PeriodLink: LOAD CID1 as ConversionID ,CID2 as ConversionIDLink Resident TEST WHERE CID1 <> CID2 AND S2 < E1 AND E2>S1;