7 Replies Latest reply on Aug 18, 2015 9:16 AM by Damian Eralio

    Creating Calendar Reports

    Nicholas Gadaleta

      Hello everyone,

       

      New to the forum, self taught in Tableau so I could use some help from you experts out there.  What I am trying to create is a calendar in excel that highlights certain dates.

      Here is what I got:

       

      - Started of with an excel file with just dates, manipulated those dates into figure 1, which simulates a calendar.

      Figure 1.JPG

      Goal: 

       

      - Create a visual within this calendar to highlight dates that include and are between "start" and "end" dates.  I have an excel file with 2 columns that have a start and end date (among other information).

       

      To give you guys an example....say I have a start date of January 1st and an end date of January 10th.  I would need a color change or a highlight within my simulated calendar for the days between (and including) the 1st-10th.  The 11th and dates after would be blank until we have another set of start and end dates. 

       

      Any thoughts or tips you can provide would be really appreciated.  

       

      Thanks,


      Nick

        • 1. Re: Creating Calendar Reports
          Damian Eralio

          Without knowing what your start and end dates look like, I would create an IF statement that tests whether each date is both greater than or equal to start date and less than or equal to end date. Use that calculated field in the "colors" mark card.

          example 1.png

          1 of 1 people found this helpful
          • 2. Re: Creating Calendar Reports
            Nicholas Gadaleta

            Thanks for the quick reply Damian, let me give that a try.

            • 3. Re: Creating Calendar Reports
              Mark Fraser

              Hi Nicholas

               

              The advice that Damien has provided looks good.

              You may be able to use TODAY() function depending on your data set/ time period.

               

              I just wanted to alert you there are a few blog posts out there... here is a good example > Creating an interactive monthly calendar in Tableau is easier than you might think May help your calendar look more like a calendar

               

              Kettan put together a collection of calendar related discussions which may be of interest The Calendar Workbook Collection

               

              Best of luck!

               

              Cheers

              Mark

              1 of 1 people found this helpful
              • 4. Re: Creating Calendar Reports
                Nicholas Gadaleta

                Damian,

                 

                Not sure why, but all of my results are 0.  I double checked the formatting of my excel files and they both match.  Am I missing something in my calculation? 

                Here I was looking for my open and close dates of 3/3/14-3/4/14. 

                 

                Results yielding zero.JPG

                 

                Thanks! 

                • 5. Re: Creating Calendar Reports
                  Damian Eralio

                  Your formula looks good.

                   

                  I also get the same result if I switch over to another month. In our example, we set Open Date to 1/1/15 and Close date to 1/10/15.

                  As long as I'm looking at Jan 2015 I will see a calendar with both Highlights and non-highlights. But if I filter to another month I will see everything is one single value.

                   

                  Because the calculation is currently treated as a continuous measure (even though it's just 0 and 1) colors are not assigned to whatever appears on your viz. So if only one value shows up, it gives it the darkest color possible. Try switching the color pill from continuous to discrete, so that your 0 values will always be one color and your 1 values will always be another color.

                   

                   

                  What are your Open and Close dates?

                  You may need to create a new sheet and just look at the value being brought back by ATTR(SE_Open_Date) just to make sure you're getting the one date you want.

                  1 of 1 people found this helpful
                  • 6. Re: Creating Calendar Reports
                    Nicholas Gadaleta

                    Ok, so I was messing around with it a bit more and it looked like changing the color pill worked.

                     

                    However, the data is only reading my close dates, no open or dates in between.figure 3.JPG

                    My concern is that because I am using 2 excel files (one with just a column of dates) my relationships between then are causing the issue.  I can only create one relationship (see the "link" icon next to SE Open).  Would you think that can cause an issue?  I was expecting to see green filled days on both 3/3 (open) and 3/4 (close).

                     

                    Thanks again for your help I really appreciate it!

                    • 7. Re: Creating Calendar Reports
                      Damian Eralio

                      I did not connect my two tables, but added them as two separate data sources.

                       

                      See how removing that connection between SE Open and Date affects your calendar. I would say that is the problem. But without having the same dataset it becomes quite difficult to guess correctly at the problem.

                       

                      Good Luck

                      1 of 1 people found this helpful