3 Replies Latest reply on Feb 21, 2013 9:43 AM by Joshua Milligan

    Comparison of totals across dimension values

    David Drees

      I'm trying to figure out how to compare values by dimension.  Here is my situation. I have a Dimension (Year) that has two possible values.  My chart shows the total revenue by account and by this category.  Account is in the Rows shelf, Year is in the Columns shelf, and Revenue is in the detail shelf.  Here is an example:

       

      Account     Year (2011/2012)

                        2011     2012

      1111          $360     $186

      2222          $213     $249

      3333          $10       $120

      4444          $150     $30

       

      What I want to do is add two other columns.  The first would show the difference between Category A and Category B.  The second would give a text description of Gain or Loss based on whether the account gained or lost revenue in 2012 compared to 2011.  In this case, I'd like to see this:

       

      Account     Year (2011/2012)     Difference     Gain/Loss

                        2011     2012

      1111          $360     $186          ($174)          Loss

      2222          $213     $249          $36              Gain

      3333          $10       $120          $110            Gain

      4444          $150     $30           ($120)           Loss

       

      How do I go about doing this?  I've worked with some table calculations, but I haven't been able to get it to work.

        • 1. Re: Comparison of totals across dimension values
          Joshua Milligan

          David,

           

          There are multiple ways of doing this and the underlying data might determine which work or not or are better.  Being able to see the underlying data structure would definitely help give specifics for your case.

           

          Having said that, I gave it a shot.  Please see if the attached workbook and see if that approach would work.  I'd love to answer any questions you might have!

           

          Best Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Comparison of totals across dimension values
            David Drees

            That is very helpful.  Thanks.  I implemented your suggestion into my data.  It gave me what I asked for, but I find that I can't filter or sort on the aggregate calculations.  For example, if I wanted to filter to look only at the "Gain" categories, this format doesn't allow me to do that.  Nor can sort on any of the calculated fields to see the highest gainers first, for example.  Any idea how I could do that?

            • 3. Re: Comparison of totals across dimension values
              Joshua Milligan

              David,

               

              It is true that you cannot filter discrete aggregate calculated fields.  However, you can filter when they are continuous.  You could create a new calculated field:

               

              IF [Sales Difference] > 0 THEN 1

              ELSE 0

              END

               

              When you use it, make sure the field is continuous and filter using "At Least" or "At Most".

               

              Another possibility, if you want to be able to provide a nice interface with a quick filter, you can use a table calculation that is based on the original aggregate calculation.  The code would look like this:

               

              LOOKUP([Sales Difference (label)], 0)

               

              Just be aware that table calculations are done after the data has returned from the data source to the Tableau data engine.  So this filter is performed after other calcs and most other operations.  That can lead to some unexpected results if you aren't prepared, such as grand totals still showing for everything (e.g. Gains and Losses, even though the Gains are being filtered out).

               

              Regards,

              Joshua

              1 of 1 people found this helpful