6 Replies Latest reply on Jun 8, 2016 11:38 AM by Joe Oppelt

    A way to relate information from one data source to another without a relationship?

    Mark Macias

      Hi Community,

       

      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).

       

      Situation:

       

      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:

       

      DYN Calculator Interface.png

       

      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.

       

      Problem:

       

      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.

        • 1. Re: A way to relate information from one data source to another without a relationship?
          Joe Oppelt

          (Note to self:  V9.1 workbook)

           

          The only way to get data from a secondary source into a sheet is to blend it in.  And your MeetingDays data source has nothing meaningful to use for blending with the main data source.

           

          That's not the end of the road though.  See Sheet 4.  I am using data from the secondary source even though it is blended on no relationships.  Yes, Tableau yells at you.  ((Change the choices in the filter and you'll see what I mean.)  You can click the "Shut up" box to tell tableau not to tell you any more.

           

          Notice that I am doing a count of the selection.  And Tableau is giving me the right answer.  You can do MIN, MAX, SUM.  One thing about bringing data from the secondary source is that it MUST be aggregated in some way.  So you won't get access to the individual values being brought in.  Just the aggregates.  Therefore you can see how many days are chosen, but not which specific ones were chosen.

           

          IF your profs are limited to two days for these special classes, you can hack through the aggregate problem.  If COUNT(Meeting PAttern) = 1, then MIN(Meeting Pattern) is the day they picked.  If COUNT=2, then MIN = one day, and MAX = the other day.  If you can have more than 2, you're hosed on this approach.

           

           

          First take a look at this and see if it can work for you.

          • 2. Re: A way to relate information from one data source to another without a relationship?
            Joe Oppelt

            (PS:  If this doesn't work, then we can talk about a way to use parameters to do what you need.  We may be able to do something that way.  You'd eliminate the second data source and just hack it up with params.)

            • 3. Re: A way to relate information from one data source to another without a relationship?
              Mark Macias

              Thanks for the quick reply, Joe, and sorry I forgot to specify 9.1--hoping to upgrade in the next week or so.

               

              Unfortunately, some of these classes do meet for more than two days a week, so we encounter the limitations of the solution pretty quickly their.  And I definitely will need to know which days are selected so that I can calculate the correct date.

               

              I originally started this off by trying to use a parameter, but immediately ran into the problem of not being able to have it display the days as check boxes.  So I then created a filter associated with the parameter to get the check boxes, but still couldn't figure out how to count days per week and parse apart which days were selected.  But I freely admit that I'm a noob regarding parameters.

              • 4. Re: A way to relate information from one data source to another without a relationship?
                Joe Oppelt

                You can make seven parameters with on-off values.  In the attached I made two of them (and I used Y and N for the two values.    I created a dashboard and used two different ways of arranging these so that your user would still have one-click selection options for each day.

                 

                If you do this, you can eliminate the second data source altogether.  You would control your  logic for which day(s) the user selected with a calc (or several calcs) to evaluate what was selected.

                 

                I will say that formatting of parameters to use this way is not a strong suit of the Tableau experience.  Still, you can make this somewhat eye-appealing, and relatively similar to the feel of a filter interface.

                • 5. Re: A way to relate information from one data source to another without a relationship?
                  Mark Macias

                  That should do the trick!  I don't know why I didn't think of it, other than the fact that I didn't think we'd have to go to such lengths in Tableau

                   

                  Thank you Joe!

                  • 6. Re: A way to relate information from one data source to another without a relationship?
                    Joe Oppelt

                    Part of the problem is that Tableau has to be all things to all data sources.  So certain limitations have to be in place to govern consistency (and allow you to blend an excel table with a relational table!)

                     

                    Some things that would have made this easier for you would be a multi-select parameter concept.  But parameters can be only one value at a time currently.  This workaround isn't so bed because the list of possible choices is maxed out at 7.  If you wanted to do this with 265 individual dates, ... uh ... no way that would be practical.  Likewise if you had a list of possible selections that would change over time.  Maintenance nightmare.

                     

                    Yup.  The workaround is a hack.  But at least you get to move forward now.