I'm engaged in a bit of an experiment but have run into a stumbling block and thought I'd throw this out to the community for consideration. I'm trying to use Tableau to create a "calculator" of sorts to fill a need that isn't currently met by our college's course management system (CMS).
We schedule classes that don't neatly fit into our term calendar. For example, Fall term 2015 ran from Sept 21 through Dec 4--an 11 week term. For a typical class, the class start day is Sept 21 (or sometime that first week) and the end day is Dec 4 (or sometime that last week). For these "regular" type classes, the census date (the date at which enrollment is captured for reporting purposes) is the 10th day of the quarter.
However, we also offer classes that may only meet for 6 weeks. We call these dynamic schedule classes (or DYN). For these, the census date is defined as "the date in which the course has met for 20% of the instructional days of the course." So for a class that meets 5 days/week for 6 weeks, the census date is the 6th class day. But for a class that meets Monday and Tuesday for 6 weeks, the census date would be the 2nd day of class (date corresponding to Tuesday of the first week) If the class meets Tuesday and Friday for 6 weeks, the census date would still be the 2nd day of class, but it would be the date corresponding to Friday of the first week.
To further complicate matters, some DYN classes start in one term and end in the next. So, it's possible to have a class start so close the end of the first term that it's census date actually occurs in the following term.
So, the crux of this is the potential impact on our class schedulers. They can't schedule these DYN class until they know in which term the census will fall. So they need a off-line calculator where they can input the start and end dates of a class, then indicate which days of the week the class will meet, and have it spit out the calculated census date and the term(s) that census date falls into. I realize that Tableau is not the ideal tool for this, but because it interfaces with our course management system data tables, and is web-based (we're on Enterprise), this seemed at first to be an "easy win."
I have a simple dashboard interface developed that accepts the inputs via filter selections:
There are two data sets involved. The first is the actual live term table in our CMS, the other a table I created to provide the user with a way to select which days of the week the class will be held (i.e., the meeting pattern). The left four filters are associated with the Term Table data set, the Meeting Pattern filter is associated with the table I created.
The problem is that I can't figure out a way to relate the meeting pattern table to the term table so that I can use all the filter data together in my calculations (which are pretty straight forward). Alternatively, I haven't found any documentation on how to refer to information from one data source to another without a relationship. In this case, from the Meeting Pattern filter I need to determine the number of meeting days per week, then the specific meeting days of the week, in order to do the calculations based on the dates input by the user. Once the census date is calculated, a lookup is performed in the Term Table to determine the term the class can actually be scheduled in.
I've attached a workbook-ignore the stuff in the CalcWorkSheet--that's based on an earlier experiment and won't apply to this project but is necessary for the ResultSheet to remain working. Most of the existing calculated fields will be replaced.