1 2 3 Previous Next 32 Replies Latest reply on Nov 25, 2014 12:38 PM by Jackie Klein

    Add reference lines to graph

    Jackie Klein

      I would like to add a reference line to both my Sales and Profit graphs here to compare the Sub-Category item selected from the filter to all Sub-Categories within a Particular Category.  For example, December 2012 - compare the 24k value for Bookcases to all Furniture Items.

       

      I want to compare to the average by month as well as the average for all dates on the graph, so there will be a a total of 3 lines on the graph.

       

      Sample workbook attached.

       

      Thanks much!
      Jackie

        • 1. Re: Add reference lines to graph
          Mark Holtz

          Hi Jackie,

           

          There are a couple ways to do this. I think you could create a duplicate data source, but I prefer to keep sources as few as possible. Linking sometimes introduces weirdness for me.

           

          At any rate, essentially, if you are using a single source, I can't think of a way to get the category total if you filter the data by a sub-category. So in essence, we need to replace your Sub-Category filter with a calculated field that displays the measure based on a parameter to make a "conditional" measure.

           

          You can edit the title to point out which sub-category you are showing. (Bring [Parameter.Select Sub-Category] into it)

          I've attached a workbook for your example. Does that help?

          • 2. Re: Re: Add reference lines to graph
            Jackie Klein

            Hi Shawn,

            Attached is a drawing of what I am trying to show (using Sales as example).  I only included Nov12 and Dec12 for the monthly averages as an example, but want a line connecting each month on the graph.  I also want a flat line showing the YTD Monthly Average (27.4K).  Also, attached is the workbook that shows how I derived these values.

             

            Again, I want these averages to dynamically update based on the Category that is selected from the Filter.  Thanks much!

             

            AvgGraph.png

            • 3. Re: Re: Re: Add reference lines to graph
              Mark Holtz

              Hi Jackie,

               

              You should be able to just divide the SUM(Category Sales) by COUNTD(SubCategory) to get the average of 35.8k you mentioned.

               

              I added that tweak to the workbook I posted before. I had to turn the data source into an extract because COUNTD is not available when using Microsoft's JET engine (i.e., distinct count is not supported from Excel sources).

              • 4. Re: Re: Re: Add reference lines to graph
                Jackie Klein

                Hmm, ok so basically it won't work unless I have the Parameter for Sub-Category?  It won't work just based off the Filters of Category and Sub-Category?

                • 5. Re: Re: Re: Add reference lines to graph
                  Mark Holtz

                  Maybe Shawn Wallwork has something for you there.

                  I couldn't think of another way to do it with a single source as it stands now.

                   

                  When you filter to a single sub-category, I don't think the view can still access the "total" information for the other sub-categories in that same "Category".

                   

                  Maybe you could modify the source to generate "Category-level" information so that every record (specific to a sub-category) has its appropriate Category-level information in-row with it.  Then, even when filtering to specific rows, you have the data you need to get the category display...

                   

                  Or, you could possibly create a 2nd duplicate data source. But I try to avoid that because it usually eventually confounds me... =\

                  • 6. Re: Re: Re: Add reference lines to graph
                    Jackie Klein

                    Well I already have a copy of the original data source in my data to use for these graphs, so if you have an example of how that would work, I'd be interested in taking a look at a sample file for that.  Thanks

                    • 7. Re: Add reference lines to graph
                      Mark Holtz

                      I think this article from the knowledge base outlines what you're trying to do:

                      Showing Summary and Detail Together

                      • 8. Re: Re: Re: Re: Add reference lines to graph
                        Jonathan Drummey

                        I've attached a workbook demonstrating two techniques:

                         

                        1. Using table calcs. We can set up the view with the necessary level of detail, then use a table calc to generate the total value, and another table calc for the filter. The drawback to this method is that all the data has to be returned to Tableau. This technique takes advantage of the order of operations in Tableau, table calc filters are processed after most all other computations are complete.

                         

                        2. Using a self-data blend. The key here is to turn off the relationship on dimension that you want to total, in this case I turned it off for Category. There can be some other complications, like if you want to use COUNTD() in the secondary (it works under certain circumstances), and what kind of filtering you might like to do between the primary and secondary.

                         

                        Jonathan

                        • 9. Re: Re: Re: Re: Add reference lines to graph
                          Jackie Klein

                          Hmm, still not seeing how this will work for what I need.  In Jonathan's example, the Total for all categories is shown where I want the Average instead.  And I'm not how Sub Category would fit into the filter as it's not shown.  Bummer.

                          • 10. Re: Re: Re: Re: Add reference lines to graph
                            Jonathan Drummey

                            Hi Jackie,

                             

                            I was demo'ing a couple of techniques, not trying to do exactly what you wanted because I don't know what that is. It seems like you want to:

                             

                            - show a measure of the sum of sales per subcategory, for a given subcategory

                            - show a measure (or reference line) of the average sum of sales per subcategory for the category of the chosen subcategory for the given month

                            - show a measure (or reference line) of the average sum of sales per subcategory for the category of the chosen subcategory for YTD (or since the beginning of the data, I'm not sure).

                             

                            Is that correct?

                             

                            Jonathan

                            • 11. Re: Re: Re: Re: Add reference lines to graph
                              Jackie Klein

                              Right on Jonathan.  For the your last point, the answer is since the beginning of the data.

                               

                              I inserted a graph above also that shows an example of graph lines that I expect to see.  Thanks

                              • 12. Re: Re: Re: Re: Re: Add reference lines to graph
                                Jonathan Drummey

                                See the attached. I used the table calc technique because the blend technique would have very little to no gain in this case. The issue is that the "higher level" aggregation per Category requires requires Sub-Category to be in the view so sales can be sum'ed per sub-category and then the higher level aggregation can be performed.

                                 

                                Avg Sales per SubCat per Month is a WINDOW_AVG(SUM([Sales])), with Compute Using on Sub-Category so it partitions on the Month and Category.

                                 

                                Then we can put that in a view, add a reference line on it, add Sum(Sales) as a dual axis, put the table calc filter Sub-Category and be done, that's set up in the TC w/ref line (less accurate) view.

                                 

                                However, that reference line is the average of each month's average, which is not a true running average. To compute a more accurate running average, I created the following two calcs:


                                Running Avg Feeder is RUNNING_AVG(SUM([Sales])), with a compute using of Month of Order Date & Sub-Category. This returns a lot of results we don't need, all we care about is the last result per category/month.

                                 

                                Running Avg Sales per Category uses PREVIOUS_VALUE(LOOKUP([Running Avg Feeder]),LAST())) for a formula with a nested compute using of Sub-Category, the LOOKUP() gets the last value in each Category, the PREVIOUS_VALUE() sets Tableau up to only run the computation once for each category/month.

                                 

                                Then this can be put into a Measure Names/Values table, see "TC view" in the attached.

                                 

                                I'm not sure how well this will scale, there's a lot of computation going on behind the scenes that is getting tossed out by the final filter. One way to speed it up would be to use a regular filter on Category, so instead of just picking the Sub-Category table calc filter the user would first need to pick a Category. Another solution would be to pre-aggregate the data at the level of Category/Sub-Category/Month of Order Date for SUM([Sales]), that way you could use a simpler set-up.

                                 

                                Jonathan

                                • 13. Re: Re: Re: Re: Re: Add reference lines to graph
                                  Jackie Klein

                                  Thanks, In my workbook attached previously there was a tab called "Furniture YTD Average Sales".  This shows the YTD Average sales for the Furniture category (flat line of $27K).  I don't see this value in your workbook.  Could you update it to show a flat line like this for the 3rd line instead of what you have?

                                  • 14. Re: Add reference lines to graph
                                    Jackie Klein

                                    I just ready your comment about the 27k not being a true average.  I guess that doesn't make sense to me.  I looks like it is a true average as it is taking the average of all months TOTAL sales (not an average of an average).

                                    1 2 3 Previous Next