I have a tough one here! I am trying to calculate how many TV shows we had on our platform for each month of the year. My data consists of a title, license window start, license window end, and the content provider. For example:
If I just create a pivot, the resulting table will only show me that I have 1 title in Jan 2020 and/or 1 title in July 2020. But what I need to know is how many titles I had for any given month for all the months in between. In the case above I want to know that this title "existed" in Jan, Feb, Mar, Apr... Solving this is beyond my skills so any thoughts or suggestions would be great.
Thank you
PS - here's some sample data to work with
title | licensing_window_start | licensing_window_end | provider_id |
Show #1 | 1/1/2020 | 7/1/2020 | TV Creator 1 |
If I just create a pivot, the resulting table will only show me that I have 1 title in Jan 2020 and/or 1 title in July 2020. But what I need to know is how many titles I had for any given month for all the months in between. In the case above I want to know that this title "existed" in Jan, Feb, Mar, Apr... Solving this is beyond my skills so any thoughts or suggestions would be great.
Thank you
PS - here's some sample data to work with
title | licensing_window_start | licensing_window_end | provider_id |
Show #1 | 9/5/2020 | 7/1/2021 | TV Creator 1 |
Show #2 | 8/29/2020 | 7/1/2021 | TV Creator 2 |
Show #3 | 8/22/2020 | 7/1/2021 | TV Creator 3 |
Show #4 | 8/15/2020 | 7/1/2021 | TV Creator 1 |
Show #5 | 8/8/2020 | 7/1/2021 | TV Creator 2 |
Show #6 | 8/1/2020 | 7/1/2021 | TV Creator 3 |
Show #7 | 7/25/2020 | 7/1/2021 | TV Creator 1 |
Show #8 | 7/18/2020 | 7/1/2021 | TV Creator 2 |
Show #9 | 7/11/2020 | 7/1/2021 | TV Creator 3 |
Show #10 | 5/23/2020 | 4/2/2021 | TV Creator 1 |
Show #11 | 5/16/2020 | 4/2/2021 | TV Creator 2 |
Show #12 | 5/9/2020 | 4/2/2021 | TV Creator 3 |
Show #13 | 5/2/2020 | 4/2/2021 | TV Creator 1 |
Show #14 | 4/25/2020 | 4/2/2021 | TV Creator 2 |
Show #15 | 4/18/2020 | 4/2/2021 | TV Creator 3 |
Show #16 | 4/11/2020 | 4/2/2021 | TV Creator 1 |
Show #17 | 4/5/2020 | 1/1/2021 | TV Creator 2 |
Show #18 | 4/5/2020 | 9/1/2020 | TV Creator 3 |
Show #19 | 3/29/2020 | 9/1/2020 | TV Creator 1 |
Show #20 | 3/29/2020 | 1/1/2021 | TV Creator 2 |
Show #21 | 3/22/2020 | 9/1/2020 | TV Creator 3 |
Show #22 | 3/22/2020 | 1/1/2021 | TV Creator 1 |
Show #23 | 3/15/2020 | 1/1/2021 | TV Creator 2 |
Show #24 | 3/15/2020 | 1/1/2021 | TV Creator 3 |