3 Replies Latest reply on Feb 1, 2017 9:39 AM by Jamieson Christian

    Get measures under the same worksheet

    ankita vaishnav

      I Have a requirement  which needs the worksheet as shown below in the screenshot . Here under

      1) Total Leadtime: we need Total leadtime Opp of Opportunity Leadtime (green circle) and Total leadtime Actual for actual Leadtime (Blue circle)

      2)Production Horizon: we need Production Opp for Opportunity Leadtime (green circle) and Production Actual for  Actual Leadtime (Blue circle)

      3)OTD: we need OTD Opp for  for Opportunity Leadtime (green circle) and OTD Actual for  Actual Leadtime (Blue circle)

       

      Highlighted in Bold above are the measures created in the Calculation.twbx file attached below.

       

      The problem am facing is , I am able to show only one measure in one worksheet, ex: OTD Opp and OTD Actual in one worksheet with header OTD adding a blank calculated field

      but how do I show ToTal Leadtime OPP and Total Leadtime Actual under Header ToTal leadtime below OTD in the same worksheet and then Production Opp and Production Actual below ToTal leadtime.

      I do not want to use 3 different worksheet for each measure and then join in dashboard. Iwant all three measures in omne worksheet as the screenshot shown below.

       

       

       

      I

        • 1. Re: Get measures under the same worksheet
          Jamieson Christian

          Ankita,

           

          If you want to do this all in one sheet, you will not be able to use Measure Names / Measure Values to make it happen. You will have to bring in a scaffolding data source.

           

          The scaffolding can be as simple as a 1, 2, 3, 4 (however many rows in your chart). It doesn't even have to be numbers; any unique row contents will do.

           

          You'll use the scaffolding on your Rows shelf, and from there, you can use INDEX() to identify which row you're on and come up with the correct values to use for that row.

           

          Here's how I put something together:

           

          1. Create your scaffold data source. I just jammed some rows into Excel and then copy-pasted into Tableau to create a new data source in the workbook.

           

          2. Create calculated fields [INDEX.Actual] and [INDEX.Opportunity] to conditionally capture the correct values, based on which row of the sheet we're on.

           

          3. Create a similar calculated field [INDEX.Label] to use as your row labels.

           

          4. Lay out your view as shown. Note that the scaffolding is the primary data source, and your data is brought in as secondary.

           

          5. Looking good so far. Now you can set up the view as a chart rather than a table, filter out any unused rows in your scaffold (I had a scaffold of 4 but only 3 types of data I'm displaying), and make it look pretty.

           

          BIG CAVEAT

          My quick-and-dirty example assumes that you do not need to incorporate any additional filtering/segmentation from the main data. If you do, the blending approach shown here will interfere. You will need to bring your data sources together some other way, such as a Tableau JOIN (but be wary of data replication).

           

           

           

          I think the root of the complexity is due to the fact that [Measure Names] and [Measure Values], being "meta" fields, are not accessible in formulas. Applications such as this would be greatly simplified if you could write a calculated field that changes based on [Measure Names]. You may want to upvote the following ideas that are related to this concept:

          https://community.tableau.com/ideas/1256

          https://community.tableau.com/ideas/1187

           

           

          Finally, I have attached a workbook showing my work, but it's in version 10.1.3, so you may not be able to view it. Sorry.

          1 of 1 people found this helpful
          • 2. Re: Get measures under the same worksheet
            ankita vaishnav

            Hi Jamieson,

             

            Thankyou for the quick resolution.

            I do have a question.  We will be populating the excel data into a SQL table once the ETL Jobs are completed. Also we have Filters added to this dashboard according to which the Data in the charts should be updated. In that case will this approach work.?

            • 3. Re: Get measures under the same worksheet
              Jamieson Christian

              Ankita,

               

              If you have an ETL process in your workflow, I suggest you leverage that to pivot your data so that your actual/opportunity columns become individual rows in your output. This will allow you to construct a view similar to how you would construct it with [Measure Names] / [Measure Values], but without the limitations that come with using "pseudo-fields".

               

              Beyond that... the example solution I provided should work fine with an SQL data source. As for filters, you may run into problems because your main data is the secondary data source in my construction, and it is blended with the scaffolding based on an empty Data Relationship (no fields are associated to the scaffolding). Usually, Tableau will insist that any filters/dimensions you try to incorporate from the secondary data source have some sort of Data Relationship to the primary data source.

               

              As I mentioned, you can also build this out as a cross-database JOIN (if you are using Tableau 10.x). Cross-database JOINs mitigate the disadvantages of blending in this scenario, but you must be extra careful to avoid data replication pitfalls.

               

              Finally, if your data is moving into an SQL data source, you can mimic the blending or JOIN solution in Tableau using a custom query. A custom query can be used to incorporate scaffolding or help reshape your data (if your ETL pipeline is not reshaping it the way you need).

               

              At the end of the day, you'll need to take all this into account and decide just how badly you want to build your report as one sheet rather than several sheets on a dashboard. There's a definite tradeoff in terms of data/report complexity, and you may find that doing the dashboard approach is the easier solution.