Selection of overlapping intervals

[dwqlik82]

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

[sunny_talwar]

It might be easier to help with a sample where we can test this out.

[dwqlik82]

Hi Sunny,

Thanks for taking the time to respond, i've had to anonymise the data in the attached so dimensions are all autonumbers rather than text and i've deleted the load script.  The fields are named slightly differently to the example as i tried to make the problem slightly easier in the example than in reality.  the bottom left table in scratchpad sheet is an example of the hard coded expression and i'm using the google timeline extension from qlik branch in case the extension objects in the scratchpad dont render

thanks again,

Dale

[sunny_talwar]

Sorry, I was not able to look at this yesterday. Can you please share the location for the google timeline extension please if it is important for me to look at your issue?

[dwqlik82]

Hi,

No problem at all, the extension isnt required at all  but for reference its from qlik branch

https://developer.qlik.com/garden/5c38a38f1f9bf60010baa65e

The more i think on this the more i think i'll need to change the model to contain a linked table of periodids that overlap each one and do it through the script.

thanks again for taking the time.

Dale.

[sunny_talwar]

I am still trying to figure out the issue here, so I can't really say if you need to change the data model or not. But for right now, I downloaded the extension... but it is saying 'Incomplete visualization'. I didn't make any changes to the app, is the app showing you a chart or do I need to change something to render a result?

[dwqlik82]

apologies, i'd searched qlik for google timeline and assumed i'd got the correct one, the very similar one below 

https://developer.qlik.com/garden/5c7d3eedcd73a200176e3e5f

is the correct one

[sunny_talwar]

It still doesn't work for me... it is expecting another dimension it seems like

[dwqlik82]

how strange, even the documentation on the extension only has 2 dimensions and 3 measures,  either way its just a visualisation of where a given user was:

essentially what i was trying to do was to select 1 FACL_ID and see all the other FACIL_ID's that had at least 1 overlapping time period for the same location. I initially thought it might be doable with a set of P() functions but my head started to go a bit strange while trying to think of how it would work.

Am increasingly thinking having a separate table with the linked periods in will be the way to go which might avoid doing all the complicated set analysis.

thanks again 

Dale

[sunny_talwar]

So, if another Facil_ID overlaps for even a second with the selected Facil_ID, you would want to show it?