5 Replies Latest reply on Jan 14, 2014 4:33 PM by John Brown

    Summing of distinct values then finding % of population with issue

    John Brown

      My data is arranged in the following way.

       

      Purchase DateProduct
      Population AttributeAttributePopulationDefective reasonDefective Population
      10/12WheelColorGreen10Cosmetic2
      10/12WheelColorBlue30DOA5
      10/12WheelColorBlue30Size2
      10/13WheelColorYellow20Size3
      11/13WheelColorRed15blah1

       

      I followed the directions listed here SUM IF is Distinct

      And now I have accurate sums of populations rather than summing the same populations more than once, now the problem is that I want to display the defective population given the total population. How would I go about getting accurate counts for say Blue given it has two distinct reasons? Ideally I want a treemap with the size of the boxes to be the population size according to atribute, which I have and the color shift the % of defective produts.

       

      Cheers!

        • 1. Re: Summing of distinct values then finding % of population with issue
          Jim Wahl

          Hi John,


          The easiest approach depends on your data and how you want to display the analysis.

           

          For example, you could use AVG(Population) if all of the rows in the partition you're aggregating over (Attribute, in this case) have the same value for Population. Then to calculate the % defective, you can use SUM(Defective Population) / AVG(Population). No table calcs required.


          This fails when you need sum the blues for multiple products, where the population for each blue row may differ. For example, if you had a blue hood product/attribute with a population 10 and only one row, you'd want the total for blue to be 40, not the AVG(Population) which would be 70/3.

           

          In this case you can use the table calc. approach you reference above:

              Population per Attribute = IIF(FIRST()==0, MIN(Population), NULL))

          calculated across a copy of the Attribute dimension (compute using Attribute (copy)).

           

          To sum all of the blues for multiple products, you'll have to have Product on the level of Detail shelf and use another table calc to sum the Population per Attribute across the Product dimension.

              Population per Attribute Total = IIF(FIRST()==0, WINDOW_SUM([Population per Attribute]), NULL))

          with compute using set to Product.

           

          A similar approach would be needed to sum the defective population across the Product dimension.    

              IIF(FIRST()==0, WINDOW_SUM(SUM([Defective Population])), NULL)

          Also set to compute using Product. (Again this assumes Product is not in the view and you don't, therefore, have a separate set of marks for each product.)

           

          See attached.

           

          Jim

          • 2. Re: Summing of distinct values then finding % of population with issue
            John Brown

            Thank you so much, your post and especially your example workbook are immensely helpful.

             

             

            John

            • 3. Re: Summing of distinct values then finding % of population with issue
              John Brown

              It seems I am having trouble with the Population Per attribute and % defective. I am pretty much copying and pasting what you have done/

              Capture.PNG.png

              • 4. Re: Summing of distinct values then finding % of population with issue
                Jim Wahl

                Hi John,

                 

                Sorry I missed this earlier in the week. With table calcs, the key is always compute using which determines have values are used in the table calculation. Population per Attribute Total is a nested table calc --- a WINDOW_SUM() of another table calc, which means you can set two different aggregation levels.

                 

                If you click this pill > Edit Table Calculation, you should see a pull-down menu for each field that allows you to set the Compute Using (aka addressing). Check that it looks like the below. If this doesn't fix the issue, could you post an updated workbook (or perhaps we could do a short screen share).

                 

                If this fixes the issue, but is still confusing, let me know and I'll try to provide a better explanation when I've got a few minutes.

                 

                2013-12-19 15-06-59.png

                 

                Jim

                • 5. Re: Summing of distinct values then finding % of population with issue
                  John Brown

                  Thanks for the answer, I ended up changing my data set to make everyones life easier.

                   

                  John