This is similar to the thread "Count In-Flight Events", but with enough of a twist that I can't figure it out (actually, I never figured THAT thread out in the first place... so maybe I'm just stuck conceptually here).
Sample raw data looks something like this:
Records with enddt=2099-12-31 are still active (as of query time).
Goal is to get a month-by-month tally of "active users as of end of month".
However, in this case, there are many historical start dates we don't need to see in results. In sample data above, I don't need to see 2010 in my result dimension -- but I need to count the IDs that have old start dates that are still active (i.e. ID A123).
So, Calculating from above data, results are (attached Excel has the logic for counting in columns to right of original data. Basically, an ID is active in a given month when: startdt <= date_dim-month AND enddt > date_dim-month)
|ACTIVE ID Count||4||3|
In reality, the backend data is around 50 million rows. Historical start dates go back about 10 years
But I only need to calculate and display IDs in the last 24 months. The source query already excludes records that are inactive prior to time I care about (enddt < 1/1/2011). That shrinks the result set down to about 40 million rows.
I can't figure out how to have an independent time dimension (that functions as my columns), and get Tableau to run through all those rows and calculate whether each one is active across the months displayed. I tried creating a two-row dimension and using the padding technique, but couldn't figure out how to get the math to work.
I've attached a packaged workbook with sampled data as well.
Glad for any help provided ! Thanks.