2 Replies Latest reply on Apr 5, 2012 8:28 AM by Kester Harding

    Displaying and filtering values vs targets

    Kester Harding

      I’m trying to build a dashboard which shows what we call ‘rendering’ (think of it as sales) against budgets (targets) by week over a period of time. Each team has a daily budget. These change over time so the budget for one team is not a constant. Budget is always 0 on a weekend or bank holiday.

       

      I have two tables of data. One lists each render along with the date, name of the fee earner (sales person), team leader, department, and whether they are based in the SA office or not. The second lists the budgets for each date individually for each team leader. Department and office data is also listed in this table.

       

      I have created a graph plotting the count of renders against the budget for the company as a whole. This involves using a dual axis graph and here I hit my first problem: The scales of the axes do not match, and they need to! I can get around it by fixing both axes but this is far from ideal.

       

      Problem no. 2 is that I now want to place quick filters so I can view rendering for a department, office or even an individual team. The trouble is that Tableau is not linking the department or office fields so the filtering is applied to the renders but not the budget. This is pretty much useless!

       

      Does anyone know of a solution to this? I’m sure that what I want is not particularly unusual!

       

      Thanks.

        • 1. Re: Displaying and filtering values vs targets
          Jonathan Drummey

          Hi Kester,

           

          Attached is an updated version of your workbook.

           

          The reason why you couldn't synchronize the axes is that the datatypes were subtly different. Tableau requires that the datatypes exactly match, and Number of Records is an integer and the Budget (1) measure was apparently coming in with a decimal. I created an INT([Budget (1)]) calculated field and was able to synchronize those axes, you can see this in the 1. Sync Axes worksheet.

           

          The Quick Filters were working as they were supposed to do, the issue is that when using data blending Tableau blends the datasources based upon the available fields in the *view*, which means on the Rows, Columns, or on the Marks Card shelves. So even though the fieldnames were the same and relationships set up, the Dept, SA, and Team Leader fields were only in the filters and nowhere else in the view, so Tableau did not blend on them.

           

          So, drag those fields onto the Level of Detail shelf, which brings them into the view. However, Tableau draws a line for every combination of values in the overall level of detail for the view, which gives you lots and lots of lines. You can see this in 2. Level of Detail mess. Tableau table calculations are the way out of this, so we can include all sorts of data in the view but only return a few datapoints.

           

          To set up the table calcs, we start with view 3. Crosstab to work them out. Working with continuous and discrete dates (blue/green pills) and table calcs can get tricky, so I created a DATETRUNC('week', [Date Concluded]) version of the field and used that in the calculations. Then I added the other fields to the Rows shelf, and created a couple of WINDOW_SUM(SUM([field])) calculations for our measures. Since we know we want to return one value for each date, I clicked on each measure and selected Edit Table Calculation, then set the Compute Using to Advanced, then put SA, Team Leader, and Dept into the Compute Using. That way Tableau will use all three fields for addressing and partition (create a new calculation for) each Date Concluded. Then we can verify that the values are the same for each date concluded.

           

          The next step is to drag the blue Dept, SA, and Team Leader pills from the Rows shelf onto the Level of Detail. This creates overlapping marks, as you can see in 4. Crosstab Mess, since Tableau is returning (the same value) for every mark. If we just tried to draw this, we'd end up with overlapping lines and that wouldn't be pretty.

           

          To prevent those from happening, we change each of the two table calcs to the following form:

           

          IF FIRST() == 0 THEN

               WINDOW_SUM(SUM([field]), 0, IIF(FIRST()=0,LAST(),0))

          END

           

          This is a technique developed by Richard Leeke at http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html to return only one record for each partition. You can see these revised table calcs in 5. Cleaned Up Crosstab.

           

          Now we can duplicate the worksheet (so we will preserve the table calc settings), drag the two measures onto the Rows shelf, set dual axes, synchronize axes, etc. and you now have a set of data that can be filtered by Dept, SA, and Team Leader from the primary and have the right totals come out of the secondary. You can see this in 6. Final View.

           

          Let me know if this works for you,

           

          Jonathan

          • 2. Re: Displaying and filtering values vs targets
            Kester Harding

            Thanks Jonathan, that was very informative and has solved the problem! Much obliged.