1 2 Previous Next 16 Replies Latest reply on Sep 20, 2013 1:35 PM by Rossella Blatt Vital

    Multiple Reference Lines: highlight third friday of each diplayed month

    Rossella Blatt Vital

      Hi,

       

      I need to add multiple reference lines to highlight the third Friday of each month.

      In particular, the chart has the following properties:

       

      Column Shelf: DATE

      Rows Shelf: DIMENSION 1, MEASURE 1

      Marks: LINE

      Color: DIMENSION 2

       

      This creates a series of  line charts (one for each DIMENSION 1). Each chart shows the MEASURE 1, colored by DIMENSION 2 (one different color for each DIMENSION 2).

       

      The x axis displays the date for several months.

       

      I would like to highlight the third Friday of each month. For example I would like to add a reference line (vertical line) in correspondence of the third Fridays of all the months displayed.  The reference lines must also be dynamic, meaning that when the view shows a different time interval (there is a time filter in the view so the user can select the period of time they are interested in), the third fridays in that interval (if any) will be displayed. 

      I tried changing the date from continuous to discrete, hoping to being able to apply the reference line to each cell or pane, but it didn't work.

       

      In case multiple lines are not an option, a conditional formatting rule might be a suboptimal solution, but I am not sure how to do that neither.

       

      Please note also that I couldn't figure out how to identify that a Friday is the third one of its month programatically in Tableau, so I created a binary field in my source data that displays a 1 when a date is the third Friday of a month, 0 otherwise. Then I use this field to create a calculated field:

       

      IIF([THIRD_FRIDAY_FLAG] = 1, [GOPW_AS_OF_DATE Format: mm/dd/yyyy], null)

       

      If you know of a way to automatically identify the third friday of a month in Tableau, that would be great. Otherwise I will keep creating the field in my source data.

       

      Thanks.

      Rossella

        • 1. Re: Multiple Reference Lines: highlight third friday of each diplayed month
          Jim Wahl

          Hi Rossella,

          I would like to add a reference line (vertical line) in correspondence of the third Fridays of all the months displayed

           

          You can't do this with reference lines, since reference lines are positioned at either a constant (specified or parameterized) or an aggregate (min, max, avg) of some measure.

           

          But you can do it with a dual-axis chart.

          2013-08-08 16-56-46.png

           

          The key part of this is the measure Friday Lines, which is equal to the highest sales value the window when it's Friday and Null otherwise. You can then add this to the graph convert it to a bar graph with the smallest-size bars.

           

          Specifically, here's what I did

          1. Created field Friday Lines =

          IF DATEPART('weekday', MIN([Order Date])) == 6  // Is it Friday (any Friday)?

          THEN WINDOW_MAX(Sum(Sales))

          END

           

          You'll likely replace DATEPART(...) == 6 with MIN([THIRD_FRIDAY_FLAG]).  Note that it has to be an aggregate, since there are likely multiple rows with the same date. The [THIRD_FRIDAY_FLAG] is always the same, so either MIN() or MAX() would work.

           

          The WINDOW_MAX(Sum(Sales)) finds the largest sales value in the line graph. The reason for this will be evident in a moment. Again, in your case with multiple dimensions, depending on how you have your axes setup, you may need to adjust the compute using to find the max value for a particular row or for all rows.

           

          2. Creating the View

          Starting with the basic line graph, I added Friday Lines next to the SUM(Sales) pill on the Rows shelf. This will create two graphs, since Friday Lines has a bunch of Nulls. you'll most likely see dots. To get vertical lines, click on the marks shelf for Friday Lines and choose bar graph. Select the size button and move the slider all the way to the left to make the bar graphs 2 px wide. I also changed the color to gray and made them 50% transparent.

          2013-08-08 16-45-04.png

           

          Note that the y-axis values are the same, since we set the height of the bar graphs to the max sales value.

           

          To combine the graphs

          • Click on the Friday Lines pill on the rows shelf and select dual axis.
          • Click the "All" tab on the Marks shelf and remove Measure Names from the Color shelf.
          • Hide the null indicator in the view.
          • Right-click on the right, Friday Lines axis and uncheck "Show Header" to hide the second axis.
          • Remove the vertical grid lines. From the menu bar > Format > Lines > Columns > Grid Lines > None

           

          If you know of a way to automatically identify the third friday of a month in Tableau, that would be great. Otherwise I will keep creating the field in my source data.

          You can do this, but I think you're better off doing it in the data source for performance and flexibility.


          There are probably a couple of approaches but you might find the day of the week of the first day of the month (IF DATEPART('day', [Date]) == 1 THEN DATEPART('weekday', [Date]) END) and adding this to the day of month. When this value = 21, it's the third Friday. For example, Sept 1 is a Sunday and in Tableau this is also the first day of the week (DATEPART('weekday' ..) == 1). So you're looking for Sept 20.

           

          This may sound easy, but it's complicated by the fact you need to use table calculations to find the first day of the month. These table calcs are somewhat expensive computationally and can make life a little more difficult when you start adding other dimensions to your view.

           

          I'll play with it a bit (or if you do, please report back).

           

          Jim

          • 2. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
            Jim Wahl

            OK, so calculating the 3rd Friday wasn't that bad. Still I think I might do it in the data source, but here's how I did it in Tableau.

             

            The basic function is DATEPART, which returns a numerical value for the part of the date you specify. So

            DATEPART('day', #2013-09-01#) == 1, since this is the first day of the month, and

            DATEPART('day', #2013-09-20#) == 20

             

            DATEPART('weekday', #2013-09-01#) == 1, since 9/1 is a Sunday and Tableau starts its weeks on Sunday.

            DATEPART('weekday', #2013-09-21#) == 6, or Friday (and in this case the third Friday)

             

            When the day of the month + the weekday of the fist day of the month == 21, it's the third Friday.

             

            So I created two calculated fields

            First Day of Month =

            IF DATEPART('day', MIN([Order Date])) == 1 THEN DATEPART('weekday', MIN([Order Date])) END

             

            3rd Friday = (boolean)

            WINDOW_MIN([First Day of Month]) + DATEPART('day', MIN([Order Date])) == 21

             

            WINDOW_MIN is required, because Tableau will calculate this measure for every row and, unless your're on a first day of the month row, the First Day of Month will be NULL and consequently 3rd Friday will be NULL. WINDOW_MIN() finds the min value (really only value) when compute using is set to the deepest (or Day) level of detail and you restart every month.

             

            I always like to specify the default table calc compute using when entering the formula. Click on the text Default Table Calculation in the upper right corner to set this:

            2013-08-08 17-31-02.png

             

            Lastly I created an updated Friday Lines measure: 3rd Friday Lines =

            IF [3rd Friday]

            THEN WINDOW_MAX(Sum(Sales))

            END

             

            Here you want to set compute using to Order Date, but do not restart every month, since we want the maximum value across all dates.

             

            Now you can replace the Friday Lines pill from the previous example with the new 3rd Friday Lines:

            2013-08-08 17-35-05.png

             

            It's a good habit to check the compute using on table calcs after measures the view. If you click on the pill and select Edit Table Calculation, you'll see a pull-down menu with both table calc measures (3rd Friday and 3rd Friday Lines) and you can verify each is set properly.

            2013-08-08 17-37-13.png

             

            Cool little problem!

             

            Jim

            1 of 1 people found this helpful
            • 3. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
              Rossella Blatt Vital

              Hi Jim,

               

              many thanks for your very helpful suggestions.

              I tried your approach and it does indeed exactly what I am looking for. Though, when I add an additional dimension on the left of the rows shelf, the height of the bars becomes wrong (see image below). I guess the problem is on the table calculation. I tried changing what the calculation must be computed across (Table across, Table down, pane down etc.), but none of them worked. 

               

              Any idea how to solve the issue?

              Thanks a lot in advance

              Rossella

               

              • 4. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                Rossella Blatt Vital

                Hi Jim,

                 

                Thanks so much! I really like this solution: simple yet very smart!

                I am wondering though if I am doing something wrong: when I replace Friday Lines with Third Friday Lines, I get the following error:

                 

                 

                These are the calculated fields: I can't see any error... could you please help me figuring out what the problem is?

                 

                1.jpg

                2.jpg

                3.jpg

                Thank you very much in advance.

                Rossella

                • 5. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                  Jim Wahl

                  I think you're on the right track with playing around with the compute using. But it's still a bit strange.


                  I usually find it easier to directly specify the compute using by dimension rather than Table / Pane / down / across.

                   

                  Friday lines should be calculated over all dimensions with the date dimension at the deepest level. If you right-click on Friday Lines > Edit Table Calculation > Compute Using > Advanced. Both GOPW_Exchnage and Date should be on the addressing side (in that order, since the addressing is hierarchical). This should be equivalent to Compute Using Table Across then Down.

                   

                  I'd also check that you don't have any other dimension in color or level of detail shelf for the Friday Lines.

                   

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                    Jim Wahl

                    I think the error is caused by not having the necessary level of detail in the view for Date.

                     

                    3rd Friday is compute using Date, but restarted every month; therefore, MONTH(Date) needs to be on the level of detail shelf as a blue pill / discrete field.

                     

                    I think I have have left this step out of my instructions (sorry!).

                     

                    2013-08-08 19-16-01.png

                    1 of 1 people found this helpful
                    • 7. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                      Rossella Blatt Vital

                      Hi Jim,

                       

                      you are absolutely right: the problem was the date field on the level of detail! Please don't apologize for your instructions: they are really very clear and detailed. I had added the date field  onto the level of detail shelf, but I didn't realize it had to be at the MONTH level. My bad: it actually makes perfect sense. Thanks for pointing that out.

                       

                      I still have some issues though: for some reason it is displaying only 2 third fridays... I will double check the formulas to figure out what I am doing wrong (see image below). Any idea of course will be very appreciated.

                       

                      Also, I am playing around with the compute using to figure out how to display the correct height for the bars of the third friday when the view has a dimension on the rows shelf (multiple horizontal charts).

                       

                      Thanks a lot for your really very helpful answers.

                      Rossella

                       

                      • 8. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                        Jim Wahl

                        I suspect the missing Friday lines are because there is no data for those dates. With a continuous date pill, Tableau will "connect the dots" but if you switch the date to a discrete, blue pill, you'll probably find no data and therefore the Third Friday calculation is NULL.

                         

                        I suspect there's a way around this, but it's not immediately obvious to me and I'm out of time today. I'll take a look at it again tomorrow.

                         

                        Jim

                        1 of 1 people found this helpful
                        • 9. Re: Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                          Rossella Blatt Vital

                          Hi Jim,

                           

                          you are right again: this approach will work only when the first of the month is present in the dataset. When this is missing or a weekend (if the date field includes only business days), then it will return null.

                          I am working on it to make it robust to these issues. In the meantime I am using the flag from the data source.

                           

                          More importantly I still couldn't figure out why the height of the bars is wrong. I understood that the problem is not the dimension on the rows shelf, but rather the coloring of each line. I modified the twb that you sent as example to show the issue.

                           

                          Thanks again very much for your useful help: not only you have already provided practical solutions but I have also been learning a lot through your posts.

                           

                          Rossella

                           

                          • 10. Re: Re: Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                            Jim Wahl

                            Hi Rossella,

                             

                            Regarding the line heights. There are some interesting things happening. If you're new to Tableau, the below combination of level of detail, stacked marks and table calcs is baptism by fire.

                             

                            I started with Sheet 1, which uses the easier "Friday Lines" rather than the "Third Friday" calc. I added department to the rows shelf and customer segment to the colors shelf, as you did above. This is Sheet 1a the attached and below.

                            2013-08-09 10-21-14.png

                            The bars vary in height and have multiple colors. Tableau is calculating the Friday Lines measure for every dimension and, by default, with bars it stacks the marks. This is more obvious if you click on Friday Lines and select show header. You'll see that the red bar has the same value as the red peak, and the visual discrepancy is caused by the axis scaling.

                             

                            The solution is pretty simple, but not at all obvious: From the top menu bar, select Analysis > Stack Marks > Off. This gives you Sheet 1b:

                            2013-08-09 10-29-38.png

                             

                            The bars are now the correct height. There are still a couple of issues: In the Furniture row, some of the bars are short because there the red dimension is null for those values. And, second, in the Office Supplies row, you see multiple colors on the bars, because the bars overlap.

                             

                            To fix the first issue, we want the table calc Friday Lines to find the maximum value over both date and customer segment. Click on the Friday Lines pill > Edit Table Calculation > Compute Using Advanced and move customer segment and date to the addressing side, but leave department on the left, partitioning side, since we want to independently scale the y-axes. After selecting the partitioning and addressing, Tableau will update the description (in blue text below) and sometimes this is helpful in understanding  the partitioning and addressing.

                            2013-08-09 10-36-01.png

                             

                            To fix the color issue, select the Friday Lines mark options and move Customer Segment pill from the color shelf to the detail shelf. Previously I had set transparency to 50% for this, but now that there are multiple marks, it'll look better set to 100%.

                             

                            SUBTLE NOTE : I think in one of the examples above (either mine or yours), the bars were not colored, which I believe was a result of removing Customer Segment from the Friday Lines level of detail. When you have multiple marks in the view, you can set the level of detail for the marks independently. But this means the Friday Lines calculation will be different. You are no longer calculating the SUM(Sales) for each customer segment, but SUM(Sales) for all segments. To keep the values / heights consistent and accurate, you need to have the same level of detail for both the lines and marks. In the case of lines, you do this with the color shelf; with the bars; the detail shelf.

                             

                            So now you have bars that are the same height as the highest peak in the line.

                            2013-08-09 11-41-57.png

                             

                            There are still a couple of problems with this view: First, the bars are thicker than before as a result of the table calc return three, identical values. Second, you still don't get a line when the underling Sales value is NULL.

                             

                            I'll come back to you with a solution to that later today.

                             

                            jim

                            1 of 1 people found this helpful
                            • 11. Re: Multiple Reference Lines: highlight third friday of each diplayed month
                              Rossella Blatt Vital

                              Hi Jim,

                              thanks again for your precious help. I was off for a week and didn't have access to my computer. I will implement your solution later today and I will try to figure out how to solve the last issues you mentioned.

                              Thanks a lot for your help.

                              Rossella

                              • 12. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                                Jim Wahl

                                Hi Rossella,

                                 

                                Welcome back. I remember "fixing" the issues above, and I'll have a few minutes later this morning to write up what I did.

                                 

                                Jim

                                • 13. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                                  Jim Wahl

                                  Hi Rossella,

                                   

                                  Here's what I did to create Version 2:

                                  1. Used data blending to add the 3rd Fridays, regardless of whether there is data on that day and
                                  2. Used a gantt chart to make the lines narrower.

                                   

                                  For the data blending, I used an Excel spreadsheet with the calendar day and a column to show whether this day is a 3rd Friday. Note that I used all of the days and not just the 3rd Fridays. This may not be necessary for the view you're creating, but it may be nice to have this 'scaffold' for other layouts. Perhaps you already have a date table in your data base the 3rd Friday indicator or where it could be added.

                                  2013-08-20 13-06-10.png

                                   

                                  After adding this data source, I duplicated the Sheet 1d worksheet, and removed the Friday Lines pill.

                                   

                                  Next I created two calculated fields for the 3rd Friday lines. These both used the [3rd Friday] field from the secondary data source. When entering the formulas, you can select this field by first selecting the new data source in the Fields pull down menu:

                                  2013-08-20 13-30-58.png

                                   

                                   

                                  3rd Friday Lines  v2 (position) =

                                  // Position of gantt bars on x / date-axis.

                                  // Using MAX() instead of ATTR() -- same result, but a little faster.

                                  IF MAX([Sheet1 (3rdFridays.xlsx)].[3rd Friday]) == "Yes"

                                  THEN 0

                                  END

                                   

                                  3rd Friday Lines  v2 (height) =

                                  WINDOW_MAX(Sum(Sales))

                                   

                                  Add 3rd Friday Lines  v2 (position) to the Rows shelf. Since it's a green, continuous pill, Tableau will create another axis.

                                   

                                  In the Marks shelf, select AGG(3rd Friday Lines  v2 (position)) and

                                  • Change the chart type to Gantt type.
                                  • Drag Custom Segment (current colors) to the detail button. We need this level of detail in the view, but don't want to color the bars.
                                  • Drag 3rd Friday Lines  v2 (height) to the size shelf / button.
                                  • Click on Size and drag the slider all the way to the left to make the rows as narrow as possible.

                                   

                                  Now to get the bars every 3rd Friday, regardless of whether there were sales on that day, click on the DAY(Order Date) pill in the Columns shelf and select Show Missing Values. Now you should have bars every 3rd Friday, but the colored lines in the sales graph are broken. Fix this by clicking the SUM(Sales) pill in the Rows shelf and selecting Format. In the left-hand format window, select Pane and at the very bottom you'll see Special Values. Select Hide (Connect Lines).

                                  2013-08-20 18-49-56.png

                                  Finally, if everything looks good. Right click on the 3rd Friday graph and select > Edit Axis > Independent axis range for each row or column. And then right click again and select Dual Axis.

                                  2013-08-20 19-12-46.png

                                   

                                  Jim

                                  1 of 1 people found this helpful
                                  • 14. Re: Re: Multiple Reference Lines: highlight third friday of each diplayed month
                                    Rossella Blatt Vital

                                    Wow Jim!! I am impressed! You really master Tableau, both at the analytical and format/visual level! I would hire you if I could!

                                    Thank you so much for your precious help. I am trying to learn as much as possible and your answers not only help me to achieve my tasks but, most importantly, are so clearly explained that I am really learning a lot.

                                    Thanks again very much.

                                    Rossella

                                    1 2 Previous Next