9 Replies Latest reply on Jul 29, 2013 12:31 PM by Jim Wahl

    Yeah..I know..another question regarding "errors" in the Grand Total

    Kevin Andrist

      While I have established pathways to handle all the individual outputs of summing and displaying supply chain "from-to" networks(Tableau to display the network itself, as well as summing weight shipped, costs..etc.. and I use the great-circle distance calculation if miles between lat/lons are of interest), I came across the parameter-driven origin city distance map in the current online tutorial "Advanced Mapping"

       

      (here:)  Advanced Mapping Techniques | Tableau Software

       

      See workbook attached.

       

      With some additional bells and whistles built in, this display could be quite useful in ad hoc origin "what if" discussions. I've been adapting the workbook and have run into the much discussed "my grand total is wrong" issue.  I've researched the issue and have come across a number of references, specifically Jonathan Drummey's excellent series of discussions here: http://community.tableau.com/docs/DOC-5069

       

      I've attempted to use this info to reach my "just give me a total of the numbers I see" goal, but no joy yet.  If I'm allowed a brief editorial comment...no doubt this topic is very interesting from an IT database & Tableau calc-coding aspect, however for the rest of us that have more than enough alternate dragons to slay.."just give me a total of the numbers I see" shouldn't be this difficult.   (as has been discussed elsewhere) This would be a classic case to a hard-coded right-click Grand Totals option..it could be called "just give me a total of the numbers I see".

       

      Anyone have an idea how to get the "numbers I see" Grand Total of the filtered(by distance) "shipped weight" in the attached workbook(Distance Detail sheet)?

       

      thx..

        • 1. Re: Yeah..I know..another question regarding "errors" in the Grand Total
          Jim Wahl
          ."just give me a total of the numbers I see" shouldn't be this difficult.

          I agree. You probably saw and voted Jonathan's idea for this functionality http://community.tableau.com/ideas/1232.

           

          The problem, of course, is that Tableau computes grand totals by removing dimensions in the view rather than summing the marks / values in the pane. Often these methods result in the same grand total value.

           

          In your case they don't because of the Road Distance Estimate filter. Road Distance Estimate is based on table calcs Latitude 0 and Longitude 0, which return the lat/long for the reference city. Table calc filters work differently than normal filters in that they are applied after the data is brought back from the database.

           

          Take an example from Superstore sales. Add Category to Rows, SUM(Sales) to Text, and apply grand totals. Everything works. And then filter on Category. Still works, because this is a standard filter which is applied at the database level before data is available in the view. When Tableau removes the Category dimension to calculate grand total, it's still operating on the filtered data.

           

          But create a table calc Category Filter -- LOOKUP([Category], 0)--and use this to filter. The rows are still filtered properly, but the grand total is not, because all of the Categories are still available to the grand total calculation. (In this case, the table calc filter is a problem. In others it's a tool. See A Jedi (Filter and Table Calc) Trick | Tableau Software.)

           

          2013-07-27 09-41-56.png

           

          The solution I came up with has two parts. First is to use a parameter for Max Distance and then use this both in the filter shelf and the measure calculations. This doesn't change the behavior of the filter, but it allows Max Distance to be used as a conditional for the other measures, which will be necessary in part two.

           

          I replaced the Road Distance Estimate filter with Road Distance Estimate Filter =

          [Road Distance Estimate] <= [Max Distance]

           

          I replaced your Road Distance Estimate measure with Road Distance Estimate GT =

          IIF([Road Distance Estimate] <= [Max Distance], [Road Distance Estimate], NULL)

           

          And shipped weight (lbs) measure with shipped weight (lbs) GT =

          IIF([Road Distance Estimate] <= [Max Distance], SUM([shipped weight (lbs)]), NULL)

           

          Now everything should look that same as your original DistanceDetail worksheet, except the grand total row is now NULL. So far, we haven't changed the grand total calc. It is still calculating over all cities and returns a value of 1325. Because of the condition in the new measure formulas, the value is NULL. If you increase the Max Distance to 1400, the GT value will appear.


          Part two of the solution fixes the grand total by increasing the level of detail. You might remember this from Jonathan's writeups. The technique is to duplicate the dimension you're totaling on and add this to the level of detail shelf. When grand total removes the primary dimension, the copy remains---essentially providing all of the marks in the grand total cell, where you can then do a WINDOW_SUM. ...


          Right-click on CityStateAbbr_rev > Duplicate. Add the (copy) to the level of detail shelf.


          Adjust the table calcs to address / compute over both the primary and (copy) dimensions. Right-click on each pill > Edit Table Calculation > Compute Using > Advanced --- move both dimensions to the addressing side.


          In addition to lots of white space, you should now see the marks in the grand total cell.

          2013-07-27 10-41-54.png


          Next, we want to sum the values in grand total. To do this, wrap the GT measure formulas in WINDOW_SUM().


          Road Distance Estimate GT =

          WINDOW_SUM(IIF([Road Distance Estimate] <= [Max Distance], [Road Distance Estimate], NULL))

           

          shipped weight (lbs) GT =

          WINDOW_SUM(IIF([Road Distance Estimate] <= [Max Distance], SUM([shipped weight (lbs)]), NULL))


          Right-click on the GT pills and click Edit Table Calculation. You'll see a pull down menu for Calculated Field. This is a nested table calc (a table calc that includes other table calcs), so you can set the compute using for each calc. Lat/Long should still address over both dimensions. But Road Distance GT should only compute over the (copy) dimension ---otherwise you'll get the sum for every row. ...

          2013-07-27 10-47-30.png


          Now it should look something like this

          2013-07-27 10-51-22.png

           

          The rows are correct and the grand totals are correct. Currently the WINDOW_SUM() is being calculated over every city in the dimension (again, since this is a table calc filter, it's all 500+ cities). The way we've setup compute using, the value is the same for all cities, so we can just print the first value using the IF FIRST() == 0 trick. Wrap both GT formulas in this statement:

           

          Road Distance Estimate GT =

          IF FIRST() == 0 THEN

               WINDOW_SUM(IIF([Road Distance Estimate] <= [Max Distance], [Road Distance Estimate], NULL))

          END

           

          shipped weight (lbs) GT =

          IF FIRST() == 0 THEN

               WINDOW_SUM(IIF([Road Distance Estimate] <= [Max Distance], SUM([shipped weight (lbs)]), NULL))

          END

           

          Now you're close except for the white space. You can eliminate this by selecting from the top menu bar Analysis > Stack Marks > Off.  Et Voilà!

           

          2013-07-27 10-57-40.png

           

          Jim

          • 2. Re: Yeah..I know..another question regarding "errors" in the Grand Total
            Shawn Wallwork

            Nicely explained Jim. Thanks for this.

             

            --Shawn

            • 3. Re: Re: Yeah..I know..another question regarding "errors" in the Grand Total
              Jonathan Drummey

              Nice work, Jim! I was working on something along the same lines of what you put together, now I don't have too, and I was able to put together an alternative that I'd been thinking about.

               

              One clarifying point is that I've come to think of the grand total computation as a separate computation with its own level of detail, and being in a separate computational path it's not subject to table calculation filters. The complicating factor in the original workbook is that once the city is chosen (by a parameter) then to get that city's latitude & longitude a table calculation is necessary to propagate that lat/long to all other cities, then from there everything else has to be table calculations (such as the distance filter) which makes getting the customized grand total more complicated because grand totals aren't filtered by table calc filters.

               

              The alternative solution (see the attached) doesn't use so many table calculations, that has the advantage of not using any parameters so it can be totally dynamic, and should be faster for larger data sources because the calcs are simpler and more filtering can occur in the data source.

               

              The starting question is, what if we could simply query a list of cities to get the latitude & longitude of a selected city and feed that back? We could do something like a parameter-based RAWSQL query to generate that, but that could be slow and would still require a parameter. The alternative I came up with is to use data blending, and Tableau v8's ability to have dimensional filters on the secondary so we can return the lat/long of the chosen city to every row. The trick for this is to control the blending so the  city is *not* one of the linking dimensions for the blend, so in effect the original source and city source are two almost-completely-independently filtered sets of data.

               

              Here's what I set up, notes at the bottom:

               

              - Made a worksheet to grab all the cities and their lat/longs.

              - Copied that data

              - Pasted it back in as a new data source, I'll call this the city source

              - Added a dimension called "Blend Field" with a value of 1 to both data sources.

              - Created the "alternate solution" worksheet

              - Put CityStateAbbr_rev from the primary on Rows

              - Added a Quick filter on the CityStateAbbr_rev from the city source. Set up the Quick Filter to only return a single value (the chosen city).

              - Turned on the blend for Blend Field, and turned it *off* for CityStateAbbr_rev by clicking the linking fields in the secondary city source.

              - Changed the Latitude 0 and Longitude 0 calcs to use the AVG(Latitude), etc. from the secondary city source.

              - Added a new calculated field Remove Chosen City Filter with the formula:

              IF MIN([CityStateAbbr_rev]) != MIN([Clipboard_20130727T090817].[CityStateAbbr_rev]) THEN 1 END

              - Put the Remove Chosen City Filter on the Filters Shelf, filtering for non-Null values.

              - Put the Road Distance Estimate field on the Filters Shelf, turning on the Quick Filter.

              - At this point, everything works except for the Grand Totals. To get those to work, I used the same technique as Jim, covered in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/. First, I duplicated the CityStateAbbr_rev calc and put the copy on the Level of Detail Shelf.

              - The Road Distance Estimate for Display and Shipped weight for display are both of the form IF FIRST()==0 THEN WINDOW_SUM([the field]) END

              - Put both those calcs in the view, with a Compute Using of the CityStateAbbr_rev (copy)

              - Went to Analysis->Stack Marks->Off to get the marks to stack and remove the extra whitespace.

               

              Ok, now for some notes:

               

              - In practice, I'd make the secondary source totally dynamic, so that way anytime a city was added it would show up in the filter.

              - Since the Road Distance Estimate calc is now a regular aggregate, you can set it to be a scoped or global filter and have it apply across the worksheets on the dashboard.

              - The Blend Field is used because otherwise there are no linking dimensions between the two sources and Tableau would keep warning us about that, so having a dummy dimension keeps Tableau quiet and happy.

              - This technique is made possible because we only want the lat/long of a single city from the secondary. If there was a desire for multiple cities to be selected from the secondary and the lat/long of each to be available to the primary, then a different technique would be needed (probably involving more table calcs).

              - The Remove Chosen City Filter is constructed the way it is because A) the calc has to be an aggregate because it is using values from the secondary and B) Tableau won't let us put a discrete regular aggregate on the Filters Shelf, so we have to make it a continuous value.

              - The one part I don't like about this solution is that the city filter from the city source has a Null value at the bottom. This is an artifact of how Tableau does data blending that I haven't been able to figure out how to remove.

              - For a really large data source, this solution should be faster because the calcs are simpler and more of the filtering is done in the data source and Tableau doesn't have to do so much computation.

               

              Jonathan

              • 4. Re: Yeah..I know..another question regarding "errors" in the Grand Total
                Jim Wahl

                Shawn -- thanks for the compliment.

                 

                Jonathan -- Thanks for the alternate solution. My first thought after looking at the original workbook was along these lines, but RAWSQL wasn't an option and the blend--which I've often equated to an Excel VLOOKUP()---wasn't exactly obvious in this case.


                I prefer your solution, mainly because it eliminates the need for parameters, but also because it simplifies the table calcs, which are always a bit problematic to maintain and verify.


                Jim

                • 5. Re: Re: Yeah..I know..another question regarding "errors" in the Grand Total
                  Jim Wahl

                  By the way,  there's something a bit strange happening with the great circle calculation.

                   

                  The Distance calculation for East Haven, CT, to East Haven, CT is NULL. In other cases, when the target and reference cities are the same, you get 0.

                   

                  This isn't such a big deal for the table, especially if you want to use Jonathan's Remove Chosen City measure to filter the reference city from the table. But it causes a problem on the map, since you want to show the origin city with a large star.

                   

                  You might want to wrap the Distance calc in a zero if null--ZN()--function.

                   

                  Kevin, I also updated the map. You may not need to use multiple marks to get the result you want. ...

                   

                  Jim

                  • 6. Re: Yeah..I know..another question regarding "errors" in the Grand Total
                    Kevin Andrist

                    Whew..thanks guys. Jim, Jonathan.. I really appreciate the time/effort you put into the solutions. The amount and complexity of the work/solution does tend to reinforce, "it shouldn't be this difficult".  Jim..prior to my original post I did locate and vote for Jonathan's idea that I alluded to initially.

                     

                    Jonathan..it may be useful (??) to somehow reference or include this discussion in your "drawing with numbers" series as it appears very close, but a different case/solution.

                     

                    I'm also getting this error when trying to open either of the last two workbooks.  Seems to me I've seen this error in the past..a year or two ago..I probably solved it on my last laptop (?)..now I'll need to figure it out again.

                     

                    screenshot_Tab error.png

                    • 7. Re: Yeah..I know..another question regarding "errors" in the Grand Total
                      Jonathan Drummey

                      Hi Kevin,

                       

                      I'm not familiar with that error...maybe it's somethng for a driver? I use Tableau on my Mac via Parallels and I had to install the Microsoft Access Database Engine 2007 driver from here http://www.tableausoftware.com/support/drivers to get text & Excel files to work.

                       

                      One thing to note is that when you take the Remove Chosen City filter out of the view (to show the chosen city on the map), you'd need to decide whether to leave it in the text table worksheet or not so the chosen city is/isn't included in the shipped weight.

                       

                      I agree, this is more trouble to put together than it could be. The first solution that Jim put together via table calcs (where the custom grand total an aggregation of a nested table calculation result) is definitely a different case than what I've covered in the grand totals series of posts, and it's something that I haven't tried to put a post together for because I haven't really come up with a generalized solution - each time I've seen this the circumstances are unique due to the table calculations being different.*** The solution that I put together uses the WINDOW_SUM() technique from the 2nd grand totals post that I'd linked to. In any case, I'd been thinking about writing a post to document the "use a secondary data source as a dynamic parameter" technique and have begun to rebuild this general kind of view using Superstore Sales, I'll get that post up sometime this week.

                       

                      *** Another complicating factor is that in Tableau we can use function X to its fullest degree on its own, but when we cross that function with other functions there can be some severe limitations. In the workbook Jim put together there were dashboards, filtering, mapping, nested table calcs, and grand totals, the solution I came up with simplified the table calcs, dashboards, and filtering by adding a data blend. It's a whole lot to keep track of!

                       

                      Jonathan

                      • 8. Re: Yeah..I know..another question regarding "errors" in the Grand Total
                        Kevin Andrist

                        Jim..no clue why, but if you do a =round(LAT,3) & =round(LON,3) in the data source excel file the CT_East Haven city shows up on the map, as do other cities that didn't show previously.

                        • 9. Re: Yeah..I know..another question regarding "errors" in the Grand Total
                          Jim Wahl

                          Cool---thanks for the update---it seemed like it might be a float / rounding issue.

                           

                          Jim