1 Reply Latest reply on Nov 20, 2011 10:58 AM by Joe Mako

    Counting elements of an aggregated field

    Daniel Thull


      although I've found a few posts on aggregating aggregate values I somehow can't seem to get to the bottom of it.


      I have a file with the columns country, brand, and sales. I aggregate sales for each brand (one brand may be sold in multiple countries) and then use a calculated field to assign each brand to a revenue band ("<100k$", "200k$" etc.) based on the aggregate. I would now like to count how many brands are in each revenue band.


      Any idea how I can do this? Could the WINDOW_COUNT function help?


      Please see the attached file as an example.




        • 1. Re: Counting elements of an aggregated field
          Joe Mako

          What you can do is use a calc field like:



          IF FIRST()==0 THEN


          with its compute using set to Brand, and then from the right-click context menu on your bin calculation pill, un-check the Ignore in Table Calculations option. This will cause partitioning on this field for all table calculations on this sheet.


          Also you can simplify your bin calculation to:



          IF SUM([sales]) <= 100 THEN "<100k"
           ELSEIF SUM([sales])<=500 THEN "500k"
          ELSEIF SUM([sales])<=1000 THEN "1000k"
          ELSE "1000k +"


          less conditional tests will evaluate quicker, because for an IF/ELSEIF/ELSE statement, Tableau matches on the first True result.


          attached is an example.