You're going to need to do some work to rearrange your data. Here's a post that covers the why: http://redheadedstepdata.io/lookup-vs-transactional/ and a list of posts that cover some different hows: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?.
The data is set up with a record for each ID when what we need is a record for each ID & Month between the start & end dates. This post is offering a new solution from the above that takes advantage of Tableau's pivot feature to avoid some data prep. (However, it does need more table calcs, that might not be what you want). Here's how I built it:
1) Duplicated your data source and pivoted it to create a "Date" column from pivot field values and "Start/End" column from pivot field names.
2) Started a view with MY(Date) on Columns and Business & Unique Identifier on Rows.
3) Right-clicked on the MY(Date) pill to turn on Show Missing Values to ensure that the dates are padded in. Tableau doesn't let us evaluate the padded Date as a regular dimension, so we have to go through a couple of steps to create a calculated field to be the Padded Date.
4) Created 1st Date with the formula TOTAL(MIN([Date])) and put that on Text Shelf with an Advanced... Compute using on all the dimensions in the view. This is a table calculation, for more information see http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations.
5) Created Padded Date with the formula DATE(DATEADD('month', INDEX()-1, DATETRUNC('month', [1st Date]))), this has a *nested* compute using on the Date. Now we have the dates fully padded out across all the months.
6) Created Counter with the formula
IF [Padded Date] >= WINDOW_MIN(IF MIN([Start/End]) == "Start Date" THEN MIN([Date]) END)
AND [Padded Date] <= WINDOW_MIN(IF MIN([Start/End]) == "End Date" THEN MIN([Date]) END) THEN
This returns 1 if the padded date is between the start or end date. This all has a nested Compute Using on the Date.
7) Created the # of Businesses calc with the formula IF FIRST()==0 THEN WINDOW_SUM([Counter]) END, this has a nested compute using on the Unique Identifier so it sums up all the Counter values for each month & Business.
Here's the workout worksheet:
8) Duplicate this sheet, drag off the 1st Date & Counter pills, turn off the tooltip for Unique Identifier, and put the Padded Date pill on the Filters Shelf to filter for 2015, and Ctrl+Drag a copy of the # of Businesses pill to the Filters Shelf to filter for Special->non-Null values:
Workbook is attached, let me know if you have any questions!
Wow, that is a very comprehensive reply. Thank you so much! I'm going to have to read this several times over to fully digest...
Thanks again for your help on this - I've gone through it and managed to replicate it using my real data, which is great.
I just had two questions as a follow up:
1.) The ability to right click and view data is now not possible, I assume, because everything is padded in? Say for example, I wanted to right click and view the 16 people in Corporate in July 2015 - is that now not possible?
2.) I want to use a date field as a quick filter slider on my dashboard, so that the user can slide between January 2014 and January 2015. I have added padded date as a quick filter to my view, but the slider becomes very compact with large gaps either side. Is there any way to instruct the filter to only include the range of 01/01/2014 - 31/12/2015 when I convert it to a slider? It seems like it should be a simple fix, but I've tried various iterations and no joy!
Many thanks again for all your help!