I'm creating a television programming tool in Qlikview for a movie channel, which has the broadcast rights to movies during certain time periods. These time periods are called "availability windows". In Qlikview, I've modeled the data to look like this:
Availability Window
-----------------------
Window ID
Movie
Availability Start (MM/DD/YYYY format)
Availability End (MM/DD/YYYY format)
Let's say I have 2 movies with 3 different availability windows each:
| ID | Movie
| Availability Start | Availability End |
|---|
| 1 | Fury | 03/01/2016 | 03/31/2016 |
| 2 | Fury | 05/01/2016 | 05/15/2016 |
| 3 | Fury | 08/01/2016 | 10/31/2016 |
| 4 | Jurassic World | 04/01/2016 | 05/15/2016 |
| 5 | Jurassic World | 09/01/2016 | 09/15/2016 |
| 6 | Jurassic World | 12/01/2016 | 12/31/2016 |
| 7 | American Sniper | 01/01/2016 | 02/28/2016 |
| 8 | American Sniper | 06/01/2016 | 06/15/2016 |
| 9 | American Sniper | 12/01/2016 | 12/31/2016 |
In the UI, I'll have a two date pickers tied to two variables: vDateStart, vDateEnd. I'll also have a listbox with two entries in it: "First time availability" and "Last time availability".
"First time availability" should search for movies with an availability window that overlaps the time within vDateStart and vDateEnd but also doesn't have any availability windows that end BEFORE vDateStart.
"Last time availability" should search for movies with an availability window that overlaps the time within vDateStart and vDateEnd but also doesn't have any availability windows AFTER vDateEnd.
So let's say the user selects vDateStart as 03/21/31 and vDateEnd as 06/21/31 and selects first time availability. Qlikview should display the availability windows with IDs 1 and 4.
If the user selects vDateStart as 09/01/2016 and vDateEnd as 09/31/2016 and selects last time availability, Qlikview should only display ID 3 since the last availability window for Fury expires within that date range. It won't show ID 5 because Jurassic World has an availability window after that time range between 12/1/2016 and 12/31/2016.
I couldn't even begin to think of the expression in Qlikview to get what I need. Please help!
