6 Replies Latest reply on Apr 3, 2012 11:24 AM by Briann Tisdale

    Shade between lines where sales fall below budget

    Briann Tisdale

      Hello All!

       

      Is it is possible to shade the area between lines to show when one falls below the other? For example when Sales fall below budget?

       

      The attached workbook uses Sales data from the Coffee Chain example data set, 'Sales' has been lowered 90% intersect the lines.

       

      Any assistance would be greatly appreciated, Thanks

       

      -Briann

        • 1. Re: Shade between lines where sales fall below budget
          Shawn Wallwork

          Hi Briann, the closest I could come to what you want is a dual axis area chart:

           

          Briann-1.PNG

          Yeah I know, not all that close. There's a discussion of this issue here:

           

          http://community.tableau.com/thread/116607

           

          But Sean's solution won't work in your case because the granularity of your data isn't fine enough, all you end up getting is clunky boxes.

           

          --Shawn

          • 2. Re: Shade between lines where sales fall below budget
            Briann Tisdale

            Shawn,

             

            You gave an idea that heads me in the right direction. Sales was placed on a dual axis as you suggested, but it was changed to a line graph.

             

            I attempted shade everything white below the minimum value, similar to this solution(http://community.tableau.com/thread/116607), but was not successfully. Maybe I'm doing it wrong; will try later this evening.

             

            Sheet 2.jpg

            Thanks,

             

            -Briann

            • 3. Re: Shade between lines where sales fall below budget
              Shawn Wallwork

              Are you talking about something like this?

              Briann-2.PNG

              Or are you talking about the minimum of both of them?

               

              --Shawn

              • 4. Re: Shade between lines where sales fall below budget
                Jonathan Drummey

                The way area charts are calculated, there's one aggregated measure (over time) and then if you have dimensions in the underlying data then those can be used to create the color bands. I don't know of any way to create new dimensions for the needed subsets of the data, i.e. "Color" for when the area chart measure is > Budget Sales and < Sales Adjust, or < Budge Sales and > Sales Adjust, and "Don't Color" for everything else.

                 

                One thought I had was to do overlapping area charts - see Overlapping Area charts in the workbook. However, you can't do overlapping area charts without a dual axis, one for each measure, and then we don't have any axes left for the Budget Sales or Sales Adjust. If you try to simplify that by having Measure Values as an area chart (you can get there by setting up an area chart with a measure, then dragging Measure Values onto that pill) you end up with stacked area chart, and that really doesn't work. You can unstack the values, but you're still stuck with only being able to add one other Measure to the dual-axis. See Measure Values Area chart for the unstacked version.

                 

                When working on this I found that Tableau suddenly started making the area chart taller than the actual totals, I've attached the workbook I was using that shows this and I'll submit it to Tableau as an issue. The worksheets are the "Starting..." and "Problem...' worksheets.

                 

                Besides using the above-mentioned reference line trick (and getting your data to a small enough date resolution and/or making the chart small enough that the reference line steps don't get in the way), the only other thing I could think of would be to make some custom SQL that would essentially reshape the data to return two rows for each date, one with a "Shading Dimension" value of "Shaded" and the other "White", and a Shading Measure that would return the appropriate values.

                 

                I created this in the attached workbook, on the "Shaded Between" worksheet.

                 

                To get this worksheet to work, I created custom SQL that performed an aggregate query over the month of date and did the following calculations:

                - found the Sum of Budget Sales

                - found the Sum of Sales Adjusted, i.e. (Sales * 0.9)

                - found the Top value (whichever of the two is greater)

                - found the Bottom value (whichever of the two is lesser)

                 

                With those calculations, the custom SQL is actually a UNION query that did those calculations twice, to return "Top" as the Shading Dimension for one row with the value of Top for Shading Measure and "Bottom" as the Shading Dimension for the second row with the value of Bottom for Shading Measure.

                 

                Now we have a datasource that will work in Tableau with the current area chart functionality.

                1. Add Shading Measure to the Rows shelf, Month of Date to the Columns shelf. Month of Date will need to be set up as a dimension.

                2. Change Shading Measure to an area chart by clicking the Area Chart on Show Me.

                3. Drag Shading Dimension onto the Color Shelf.

                4. Go to Analysis->Stack Marks->Off. This is necessary because area charts are stacked by default, yet we want our lines for Budget Sales and Sales Adjusted to be unstacked.

                5. On the Shading Dimension color legend, set the "White" dimension to be on top of "Shaded" so the marks will overlap.

                6. Edit the colors so Shaded is grey and White is white (you'll need to double-click on White in the Edit Colors dialog to bring up the custom color setting dialog).

                6. On the Color shelf, change the Transparency to 100%. Now you have a shaded line.

                7. Turn off updates.

                8. Add Measure Values to the Rows shelf. Delete all measure values from the Measure Values card except for Sum Budget Sales and Sum Budget Adjust.

                9. Set the aggregation for Sum Budget Sales and Sum Sales Adjust to ATTR(), since we don't want to double-count them.

                9. Turn updates back on.

                10. On the Rows shelf, set the Mark Type for the Measure Values pill to Line

                11. Drag Measure Names onto the Color Shelf for the Measure Values marks.

                12. On the Rows shelf, set the Measure Values pill to Dual Axis.

                13. Right-click on the right-hand (Measure Values) axis and select Synchronize Axis.

                 

                screenshot1.jpg

                 

                From here, you'd probably need to clean up the axes and headers, not show the color legend for the Shading Dimension, clean up the tooltips, etc.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Shade between lines where sales fall below budget
                  Briann Tisdale

                  Awesome!!! Jonathan's solution will work perfectly.

                   

                  Thanks for all your assistance.

                   

                  -Briann

                  • 6. Re: Shade between lines where sales fall below budget
                    Briann Tisdale

                    FYI - The attached example has the Custom SQL slightly modified to only shade area where sales fall below budget. the 'Top' field on the 'Shaded' part of the union quary was changed from

                         IIf([Sum Budget Sales]>=[Sum Sales Adjust],[Sum Budget Sales],[Sum Sales Adjust]) AS [Top]

                              to

                         IIf([Sum Budget Sales]>=[Sum Sales Adjust],[Sum Budget Sales],[bottom]) AS [Top]

                     

                    Thanks again for your assistance !!!

                    Shaded Sales below Budget.jpg