2 Replies Latest reply on Jul 15, 2013 5:29 AM by Jonathan Drummey

    CALC HELP - how to add/subtract row-level data

    David Andrade
    David Andrade Jul 12, 2013 7:30 AM

      Need some help with a table calc I just can't seem to figure out right now.

       

      I have a 5 columns: Brand, Profile, DataDate, EntryType, and Visits

       

      I need to filter the data first by Brand and Profile

       

      Then I need to look at it just for May 2013 data

       

      I have a couple of EntryTypes which include [A,B,C,D,E,F,G]

       

      I view all the entrytypes by the sum of visits, which you'll see I've done in Tableau up to this point.

       

      What I want to figure out how to do is create a new EntryType, "H", which takes the sum of B, C, D, F, and G and subtracts it from the sum of A.

       

      Take a look at how my data is set up (assume data structure cannot be changed) and help me figure out how to calc this new EntryType.

       

      Thanks!

        • 1. Re: CALC HELP - how to add/subtract row-level data
          David Andrade
          David Andrade Jul 14, 2013 10:31 AM (in response to David Andrade)

          My wicked smaht co-worker Amanda Gessert helped me work out a possible solution to my question, so I thought I'd share so anyone else with the same question can see what we've done here.

          • 2. Re: Re: CALC HELP - how to add/subtract row-level data
            Jonathan Drummey
            Jonathan Drummey Ambassador Jul 15, 2013 5:29 AM (in response to David Andrade)

            Here's an alternate solution, which gets at Amanda's point about adding a new value to the list of possible Entry Types, it's a variation of the Custom SQL for custom grand total solution at http://drawingwithnumbers.artisart.org/customizing-table-calculations-part-3/. I first  copied out the data and pasted it in, then used Custom SQL to duplicate the data, adding a column to identify the Source (original or new header). Once I had that, then I created a calculated field called New Entry Type with the following formula: IF [Source] == "Orig" THEN [EntryType] ELSE "H" END. Then the New Entry Type is used in the text table instead of the Entry Type. You can still filter on the original entry type and the totals will remain.

             

            Another technique instead of duplicating all of the data would be to UNION a SELECT DISTINCT query to get all the combinations of date/Brand/Profile, add a new Entry Type H, then use a table calc to assign a value to H.

             

            As a general approach, the maximum level of detail in a view is the combination of distinct values of your dimensions, Tableau won't increase the level of detail beyond that. So, to show "extra" rows, we need to find a way to increase the level of detail. Using a custom query or subquery to UNION in some extra data is one technique, another is making use of the Grand Total. A third technique is to go to the worksheet level and use a separate worksheet for H then merge the two on a dashboard.

             

            Jonathan