3 Replies Latest reply on Oct 25, 2018 2:05 AM by Simon Runc

    Overlay line plots on a single figure

    Danish Haroon

      Hello

       

      I am working on the following sheet:

      sheet_snap.JPG

      When no Vendor and/or State is selected, it is performing the desired behavior i.e. aggregation in the Time series plot.

      However when more than one vendors and/or States are selected I want a overlay time series representation for comparison purposes. Currently it is showing a aggregation on that level. How can it be done?

       

      By overlay time series representation I mean the following:

      example_overlay.png

      The packaged file is attached along with this email.

       

      Danish

        • 1. Re: Overlay line plots on a single figure
          Simon Runc

          hi Danish,

           

          So one way we can do this is by using a sheet swap, where we use formulas to pick up if all Vendors are selected.

          I duplicated your sheet and brought Vendor onto the colour tile in one of them, so we get a line per vendor.

           

          I created one formula like this

          [Show Vendor Level]

          SUM([Total Number of Vendors]) > WINDOW_SUM(COUNTD([Vendor Name]))

           

          set it up like this

          Put it in the filter and set to true.

           

          For the other sheet I set up

          [Show Total Level]

          SUM([Total Number of Vendors]) = COUNTD([Vendor Name])

           

          again I brought this into the filter and set to true. I then put them both on a dashboard, and when not all the vendors are selected the sheets swaps to show the vendor level one.

           

          Hope that helps. One thing to note in 2018.2 there is a bug where it can generate a 'unable-to-complete-action-table-arrays-must-be-of-the-same-size' error, which Tableau are investigating. Here is the current fixes https://kb.tableau.com/articles/issue/unable-to-complete-action-table-arrays-must-be-of-the-same-size

          https://kb.tableau.com/articles/issue/unable-to-complete-action-table-arrays-must-be-of-the-same-size

           

          I've downgraded this to 2018.1

           

          Hope that all makes sense, and does what you need.

          • 2. Re: Overlay line plots on a single figure
            Danish Haroon

            Thank you so much Simon

             

            Had the following queries:

            Both of them are calculated fields, and will be set as filter later on. Right?

            By SUM([Total Number of Vendors]), you mean sum of Expense Total Amount across all vendors?

            Can you help me understand why are we doing SUM([Total Number of Vendors]), and WINDOW_SUM(COUNTD([Vendor Name])) in the first path?

             

            Thank you

            Danish

            • 3. Re: Overlay line plots on a single figure
              Simon Runc

              Yes these will both be used as filters.

               

              So great questions.

               

              I didn't mention the main calculation here

              [Total Number of Vendors]

              {COUNTD([Vendor Name])}

               

              btw, we could have written this

              {FIXED: COUNTD([Vendor Name])}

              so the above is just a short cut for a dimension-less FIXED LoD

               

              This is a FIXED LoD, which returns the total number of vendors in the data. FIXED LoDs are computed before any regular filters, so even when we filter to a Vendor name the value of this calculation still equates to the total. So we can use this to compare with how many are in the view (non LoD)

               

              The reason we wrap it in a SUM for each formula is that the other side of the formula is an aggregate (WINDOW_SUM or COUNTD) and we can'y mix aggregates and non-aggregates, so we just wrap it in a sum.

               

              on the WINDOW_SUM(COUNTD([Vendor Name]))  formula

               

              In the sheet where we have Vendor ID in the Level of Detail, if we just used COUNTD it would do a COUNTD for each vendor (so would equal 1 for each vendor), and as we want to know how many are in the view (so we can compare to the total) we use the Table Calc WINDOW_SUM, so it can SUM up all those 1s.

               

              Hope that makes a bit more sense