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

    Counting elements of an aggregated field

    Daniel Thull

      Hi,

      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.

       

      Thanks,

      Daniel

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

          What you can do is use a calc field like:

           

           

          IF FIRST()==0 THEN
          
           WINDOW_SUM(AVG(1),0,IIF(FIRST()==0,LAST(),0))
          END
          


           

          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 +"
          END
          


           

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

           

          attached is an example.