6 Replies Latest reply on Nov 19, 2018 5:17 AM by Tsz Wai Lam

    How should I create a calendar with only one data point?

    Tsz Wai Lam

      Hello everyone,

       

      Currently the data looks something like this -

       

      NameCalendarCalendar Start Date
      John6 Week Rotation1/12/2018
      Amy5 Week Rotation1/26/2018
      Daniel5 Week Rotation2/12/2018
      AdamNo Rotation1/15/2018
      Ada6 Week Rotation1/19/2018

       

      If the person is on a 6 week rotation, the person's schedule would be A > B > C > D > E > F > A > B > C > D > E > F  and repeat

      If the person is on a 5 week rotation ,the person's schedule would be A > B > C > D > E > A > B > C > D > E and repeat

      and the rotation would start at the calendar start date. I was wondering if there is any chance to make a calendar that I can see day by day which shift that person is on, with only one line of data to populate the calendar.

       

      Thank you in advance !

        • 1. Re: How should I create a calendar with only one data point?
          Shinichiro Murakami

          Hope this helps.

           

           

           

           

           

           

           

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: How should I create a calendar with only one data point?
            Hari Ankem

            Here is another solution:

            1.png

            I have joined your data with a data file containing the rotation numbers from 1 to 30, so that we can have a schedule for the next 30 days.

            1.png

             

            This data has been joined as shown below:

            1.png

            I have a calculated field called Date with the formula DATE(DATEADD("day",[Rotation Number],[Calendar Start Date]))

             

            And another calculated field to display the schedule as shown below:

            1.png

             

            Hope this helps. The packaged workbook is attached along with the data files I have created.

            1 of 1 people found this helpful
            • 3. Re: How should I create a calendar with only one data point?
              Tsz Wai Lam

              Thank you, I was wondering if there is a way to do it without joining any data

              • 4. Re: How should I create a calendar with only one data point?
                Shinichiro Murakami

                I cannot imagine the logic/view without having the date scaffold.

                 

                If this helped, could you please mark my answer as correct/helped to close the thread not from inbox but from original post.

                 

                 

                 

                Thanks,

                Shin

                • 5. Re: How should I create a calendar with only one data point?
                  Chris Chalmers

                  Hari and Shin's solutions are by far the easiest, but if you don't want to join in additional data, it can be done using Data Densification. It's not easy, but it works. Here is the end result:

                   

                  CalTwb_Result1.PNGCalTwb_Result2.PNG

                  Note that one of the people is on a separate viz. I haven't figured out how to fix this, but I think there is a way.

                   

                  Making this viz requires a few tricks.

                   

                  Show Missing Values

                  This is the heart of data densification.

                  1. Drag [Calendar Start Date] to rows or columns.
                  2. Open the dropdown on the pill and select "Exact Date".
                  3. Open the dropdown on the pill again and select "Discrete".
                  4. Right click on the date axis and select "Show Missing Values".

                   

                  Selecting "Show Missing Values" causes Tableau to generate a new row of data for every date between the earliest and latest dates in the data. It takes sparse data and makes it denser - that's why it's called Data Densification. Table calculations can then be used on this densified data.

                   

                  CalTwb_ShowMissingValues.PNG

                  Extending the Date Range

                  You're making a calendar, so you want the dates to extend farther into the future than the latest date in your data. This is probably the biggest hack of the solution. You have to pick one person in your data to use as a dummy. You're going to make a calculation that returns the start date for everyone except that person. The dummy's "start date" will be set to some date far in the future that you want your calendar to show up to. Data densification will fill in all the dates up to that date. The dummy's real start date will be missing from the resulting viz. I think there is a way to get it back, but I haven't figured that part out yet.

                   

                  I picked my dummy as the person whose start date is already the latest in the dataset. This is arbitrary, you can pick anyone you want.

                   

                  CalTwb_IsDummy.PNG

                   

                  Create a parameter called [Max Date] and set it to several months in the future, and then create your fake start date.

                   

                  CalTwb_DateExceptDummy.PNG

                   

                   

                  Now do the data densification steps above, but with the fake date calculation. You should see many more dates appear, all the way up to [Max Date]. Now drag [Name] and [Is Dummy] onto the opposite axis, and you should end up with something like this:

                   

                  CalTwb_BaseViz.PNG

                   

                  Crazy Table Calcs

                  Densified data can only be acted on by table calcs. This limits our access to some of Tableau's most powerful features, including LOD calcs, so we're going to have to get creative. I'm not an expert on table calcs, so there may be more efficient ways to do it, but here's what I ended up with:

                   

                  CalTwb_ShiftLetterTableCalc.PNG

                  [Shift Length in Days] is a parameter that I have set to seven, and [Rotation Length in Weeks] is just the integer in the [Calendar] field: INT(SPLIT([Calendar], ' ', 1)).

                   

                  Summary of the above calculation:

                  1. Do a running sum of the number of records by date. There is one record per name, so for each name this produces all zeroes before the start date, and all ones after that.
                  2. Do a running sum of the result. This produces a counting sequence (1, 2, 3, 4, etc) starting at the start date for each person.
                  3. Divide by the number of days in a shift (set by a parameter, I assumed seven).
                  4. Take the ceiling minus one of this number (subtract epsilon, cast to int). The subtraction actually happens a little early; doesn't matter. Now each shift has a unique number.
                  5. Take the remainder of dividing the number by the rotation length for the current person. This bounds the maximum value, so that the shift ID resets at six or seven or whatever the rotation length is.
                  6. Convert the shift ID to a letter (add the ASCII value of a capital 'A', and then cast to CHAR).
                  7. Do all of that if there is a rotation - if there isn't, stick a "None" value on every mark after the start date for that person.

                   

                  Drag this calculation to Text, then right click on the pill and select Compute Using > Table (down). You should see something like this:

                   

                  CalTwb_AlmostViz.PNG

                   

                  Add some formatting (I recommend the Gantt chart trick from Shin's answer), and create another similar viz to show the dummy, and you should be set. Now all that's left is to get the dummy person back onto the same viz as everyone else, which I still haven't figured out.

                   

                  I attached a 2018.1 workbook showing this technique.

                   

                  Happy Vizzing!

                  -Chris Chalmers

                  3 of 3 people found this helpful
                  • 6. Re: How should I create a calendar with only one data point?
                    Tsz Wai Lam

                    Thanks a lot, I will definitely check this out