Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Just wondering if anyone has done something similar trying to show the periods that overlap the ones in a current selection (or a set of periods defined using set analysis in an island table)
i currently have a table similar to the below:
PeriodID | StartDate | EndDate | Location | User | RowCount |
1 | 43520.382638889 | 43885.414849537 | Here | Rod | 1 |
2 | 43764.979166667 | 43853.701967593 | There | Jane | 1 |
3 | 43783.814583333 | 43857.541805556 | Here | Freddy | 1 |
... | ... | ... | ... | ... | ... |
What i'd like to be able to do is when a user is selected show all the other periods where the intervals overlap for the same location for that user.
I'd done something as a test using a hard coded periodID that appears to work but that wont scale up to doing multiple periodIDs (i dont think at least )
Sum({1<PeriodID=
P({1<EndDate={">$(=only({<PeriodID={7510}>}StartDate))"}
, StartDate={"<$(=only({<PeriodID={7510}>}EndDate))"}
, Location={"$(=only({<PeriodID={7510}>}Location))"} >})>}RowCount)
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...) .
yours hopefully
Dale
Hi,
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.
Dale
Hi,
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
Resident Conv;
LEFT Join
LOAD ConversionID as CID2
,DerivedStartDTTM as S2
,DerivedEndDTTM as E2
,Location as LocationTest
Resident Conv;
NoConcatenate
PeriodLink:
LOAD CID1 as ConversionID
,CID2 as ConversionIDLink
Resident TEST
WHERE CID1 <> CID2 AND S2 < E1 AND E2>S1;
drop table TEST;