5 Replies Latest reply on Sep 16, 2016 1:52 AM by Samuel Gee

    How do I aggregate a dimension?

    Samuel Gee

      Hi!

       

      This is my biggest ongoing problem with tableau.

       

      I have [Sales] and [Title].

       

      I want to say "If the total sales are at least 500, then include the title, else ignore it".

       

      So I write

       

      IF sum([Sales]) > 500 THEN [Title] ELSE null END

       

      The obvious problem I then get though is that I can't mix aggregated and non-aggregated in an IF statement.

       

      So I wrap the [Title] in an ATTR which is the only function I know of that can aggregate a dimension and of course it returns * because I'm asking for multiple elements to be returned.

       

      I think this is a fundamental misunderstanding on my part of the way Tableau works. I just want to say - for any given time period, if the total sales of a title are over x then let me know the title, but I don't seem to be able to.

       

      I don't think I can write

       

      IF [Sales] > 500 THEN [Title] ELSE null END

       

      As whilst it's not mixing the aggregations, it's saying "If any one row has a sale value of > 500 then return the title in that row", and not "if the sum of all sales...etc".

       

      Any guidance would be appreciated.

       

      Thank you very much,

       

      Sam

        • 1. Re: How do I aggregate a dimension?
          David Li

          Hi Samuel! It's a little difficult to help without more information on what you're trying to do. Do you have a packaged workbook that you can share?

           

          The issue here, as you've said, is that the ATTR aggregation only works if there's one unique dimension key in each cell in your sheet. In order for your first method to work all the time, [Title] would need to reference a dimension that is as or less granular than your sheet's level of detail. For instance, let's say that you're working with dates as dimensions and you've set up your sheet so that each cell row is a different month. Your [Title] field can only reference months, years, etc. It won't work with days, weekdays, weeks, etc.

           

          If you can give me more specific information what you're trying to accomplish, I might be able to help further.

          • 2. Re: How do I aggregate a dimension?
            Joshua Milligan

            Samuel Gee wrote:

            ...

            I want to say "If the total sales are at least 500, then include the title, else ignore it".

            ...

             

             

            Samuel,

             

            That statement makes me think that what you might want to do is filter based on an aggregation at the level of detail of Title.  When you place Title on the Filters shelf, you'll get some options as to how to filter.  Using the Condition tab, you can specify a By field condition, which in this case is Sales where the Sum is >= 500.

             

             

            Then, your view will only show Titles that have a sum of sales greater than $500.

             

            There are some other approaches (Sets, LoD, filtering Sales as an aggregate based on a Title view level of detail) that would also work, but filtering Title is one of the most straightforward that gives you a lot of flexibility in the view (you can show Titles or any other level of detail  in the view).

             

            Hope that helps!

            Joshua

            1 of 1 people found this helpful
            • 3. Re: How do I aggregate a dimension?
              Aron Fejes

              I would create a calculated field  to evaluate whether the row meets your criteria, and then you can decide what to do with it.

               

              1. Go to Analysis > Create Calculated Field

              2. Enter the formula If Sum (Sales) > 500 then 'Yes' else 'No' End

               

              Drag this calculated field to the dashboard if you want to group by this result or the filter if you want to only display those with > 500

               

              If you group the worksheet by the calculated field, and don't group it by anything else, it should sum all of the "No" results - essentially giving you the line-item detail for those records that are 'Yes' and one row for all of the 'No' records. I think...

              • 4. Re: How do I aggregate a dimension?
                Samuel Gee

                Thanks Joshua!

                 

                I'm interested in the more complicated ways, because ideally I'd be using this in a calculated field! Would you mind speaking more to them?


                Thank you,

                 

                Sam

                • 5. Re: How do I aggregate a dimension?
                  Samuel Gee

                  Hi Aron,

                   

                  Thank you, but I'm not sure that helps me! I want to filter based off the aggregated sales by each title - a title with 10 sales over 50 rows would still be relevant, a title with 100 sales over 100 rows would not. I'm not clear on how grouping all the <500 sales into one row would assist?