4 Replies Latest reply on Nov 5, 2018 4:31 PM by swaroop.gantela

    Scatter chart reference area

    Alexey Virov

      Hello, awesome Tableau community.

       

      I have built a scatter chart that shows the (hard) life of sales managers - namely the number of their month of work on X axis, and the monthly sales that they have so far achieved on Y axis. The goal is to see which managers fall below/go above expectations.

      The chart itself is fine, but now we need to add some reference to show these expectations on the graph, right?

      The expectations come from The Boss in the form of two lines - one 'you are fired if you go below' and the other 'I owe you some bonus money'. These lines are basically amount of sales you are expected to have at the N month of your work.

      Trouble is, I can't get these lines look good on the graph. I also can't get them on every month tick, only on those, that have managers.

      I would really like to have the reference lines look like a widening area from start to end, here are some pictures:

      Now:

      Dashboard 1.pngDashboard 2.png

      Desired:

      Dashboard 3.png

      My first instinct was to create an iterator for range(1, 31) and create two calculated fields that yield a number based on the iterator value.

      I failed miserably at that, and just settled for making another data source, connecting it to managers on 'DATEDIFF('month', [manager_begin], TODAY())' = 'plan.month'. Understandably, it shows dots for rows with managers only and is generally a bad approach.

      If someone know how to draw these lines, you caould help a number of people get their extra money. Don't mind those below the lines - these are either critters or ghosts of former employees, whose contracts have not been redistributed yet.

        • 1. Re: Scatter chart reference area
          swaroop.gantela

          Alexey,

           

          Firstly, your post was very entertaining and humorous! Enjoyed reading it.

           

          Your set-up is a bit complex. I'm not sure that I fully understood the impacts of the join.

          But it did feel like to me that the nature of the [trash] filter was causing problems.


          I tried a different filter:

          IF { INCLUDE [manager] : SUM([trips_last_month])} > 20 THEN [manager] END

           

          which I placed on the filter shelf as an attribute.
          This took the place of the two formerly separate filters of [trash] and [manager].

           

           

          But in the filter check box list, it added a new entry which is " * "

          and appears to represent the data from your external plan source.

          I'm not sure if that is workable for you. If not, will I'll look into other options.

           

          Wondering if there is another way to exorcise the ghosts.

           

          Please see workbook v18.3 (is that your version too?) attached in the Forum Thread:

          Scatter chart reference area

           

          286649lines.png

          1 of 1 people found this helpful
          • 2. Re: Scatter chart reference area
            Alexey Virov

            Swaroop,

             

            That is a very nice approach, definitely a great improvement to my own design!

            I have updated my actual dashboard with it already, thank you!

             

            The only thing still lacking, when there are no managers with a certain [exp] (like 16 and 17), the reference lines also skip these points, going straight from 15 to 18. It does not look too bad in this very case, since we still get continuous lines and there is no one to evaluate on these skipped exp months anyway. Still would probably better to improve the graph integrity by showing all available reference points.

             

            I was thinking of a different approach in general:

            First, it seems that I did structurally wrong to link plans to every manager and to use an external data source for plans at all.

            Is it possible to create a calculated dimension A that would simply return a row of numbers from 1 to 24? Like, a generator? Like (a for a in range(1, 24))?

            Then we create a calculated measure B, which returns a number based on A. Like (IF [a] = 1 THEN 75 ELSEIF ... END)

            Then we put those on the graph and receive a set of points on the chart corresponding to plan values.

            Or no generators in Tableau?

            • 3. Re: Scatter chart reference area
              Alexey Virov

              Found this article

              https://kb.tableau.com/articles/howto/displaying-dates-not-in-your-data-source-on-an-axis

              only somewhat related to my isue, but still helped.

               

              Reordered data source files so that they go: plan - manager list - sales

              Now we have plan points on every point - even with no managers. Trouble now - can't seem to filter out managers with sales below, say, 20, but it is a minor thing compared to the initial variant.

              1111.PNG

              My gratitude to Swaroop again, was looking in the totally wrong direction before your post.

              • 4. Re: Scatter chart reference area
                swaroop.gantela

                Alexey,

                 

                I don't have a good grasp of your data sources,

                but wanted to suggest another method (maybe you have already tried and discounted it)

                of unioning the external plan data to your measured data.

                 

                That way, it would be independent of the managers.

                This allows for all the plan points to show,

                and for filtering out of low count managers.

                 

                (You can give the values the same column name, but that might not actually be necessary)

                 

                The unioning will auto-generate a new field called [Table Name] which you can use in calcs.

                I haven't actually tried it out, so it may not work, but in theory,

                you get the plan lines by something like:

                IF [Table Name] = "Plan" THEN [Value] END

                 

                And conversely for manager points

                IF [Table Name] = "Data" THEN [Value] END

                 

                Then plot on dual axes as you have done.