11 Replies Latest reply on Aug 6, 2015 11:24 AM by Jonathan Mills

    Aggregation delemma

    Jonathan Mills

      I'm trying to aggregate the following data, in order to get a percent of total.  The data below is an example of designs, and how many reviews have been submitted per design.  I'd like to calculate the number of first time Review Response #, and then calculate the percent of total:

      DesignerDesign #Review Response #

      Joe

      12345

      1

      Joe125431
      Joe12543

      2

      Mark543211
      Mark543212
      Mark987651
      Mark987441

       

       

      Desired result would be:

       

      DesignerReviewsFirst Time Reviews% of Total
      Joe3133%
      Mark4250%

       

       

      Thanks in advance.

        • 1. Re: Aggregation delemma
          Rody Zakovich

          Hello Jonathan,

           

          Which Version of Tableau are you on?  Also, is the first time Review based on the Review Number? i.e. The MIN review number represents the first review?

           

          If you are on V9, and I made the correct assumption about the First Review, then you can use an LoD.

           

          For first time review you can use a calc like this

           

          COUNT(IF [Design #] = { INCLUDE [Designer] : MIN([Design #]) }

          THEN [Design #]

          END)

           

          And for the % of total you can use a calc like this

           

          COUNT(IF [Design #] = { INCLUDE [Designer] : MIN([Design #]) }

          THEN [Design #]

          END)

          / COUNT([Design #])

           

           

          Or simply reference your calc

           

          [First Time Reviews] / COUNT([Design #])

           

           

          8-6-2015 12-22-55 PM.png

           

          Hope this helps.

           

          Regards,

          Rody

          1 of 1 people found this helpful
          • 2. Re: Aggregation delemma
            Jonathan Mills

            Thanks Rody,  it's much closer to what I had before.  Your assumptions were correct, however the issue I see with your solution is that all desing's have at least 1 review, whereas I'd like to capture the designs that ONLY have one review.

             

            Per my example data above and your solution, it's including all design reviews numbered with a 1, as the Design's all have at least 1 review.

             

            ie

             

            DesignerReviewsFirst Time Reviews
            Joe32
            Mark43
            • 3. Re: Aggregation delemma
              Rody Zakovich

              Hey Jonathan,

               

              I'm a little confused by what you mean here, but if you only want to count a review when it has a Response of 1, then you can do something like this.

               

              First Time Response =>

               

              IF [Review Response #] = 1

              THEN [Design #]

              END

               

              8-6-2015 12-48-39 PM.png

               

              Is this more in line with what you needed? You can substitute that calc in the LoD if you need to.

               

              Regards,

              Rody

              • 4. Re: Aggregation delemma
                Rody Zakovich

                Actually, if that was what you were looking for, you don't even need the LoD

                 

                COUNT(IF [Review Response #] = 1 THEN [Design #] END)

                / COUNT([Design #] )


                8-6-2015 12-53-42 PM.png


                Rody

                • 5. Re: Aggregation delemma
                  Jonathan Mills

                  Thanks again Rody,

                   

                  I think your first answer was more along the right track, however I'm running into some issues as the totals for first time reviews doesn't add up properly.  I've attached a copy of my workbook.

                   

                  Appreciate the help!

                  • 6. Re: Aggregation delemma
                    Rody Zakovich

                    I apologize, I don't know why I am confused here.

                     

                    So you want the Number of First Time responses, as well as, % of total responses.

                     

                    Using the Example Below, Dan Jiang, has 3 First Time Responses??

                     

                    8-6-2015 1-32-10 PM.png

                     

                    He has 6 total responses....Therefore his % of Total would by 50%

                     

                    Am I understanding what you want?

                     

                    Rody

                    • 7. Re: Aggregation delemma
                      Jonathan Mills

                      not quite, I'm trying to figure out which designs had a total of 1 review.  Essentially, I'm counting the designs which passed on their first review, and didn't have additional reviews.

                       

                      Therefore I expect the following:

                       

                      Designer NameTotal Number of ReviewsTotal number of first time reviews
                      Dan Jiang60
                      Davy Zhuang31
                      Earl Rogers53
                      Mohamed Abusbeaa33
                      Patrick Vincent175

                       

                       

                      I thought of something along the lines of Summing the total review responses equal to 1, however couldn't get the LoD to work.

                      • 8. Re: Aggregation delemma
                        Ben Page

                        Hi Jonathan,

                         

                        Is this what you're looking for?

                        aggregation delimma.PNG

                        I've attached my workbook.

                         

                        Ben

                        • 9. Re: Aggregation delemma
                          pooja.gandhi

                          That would just be : { fixed [Designer Name], [Design#] : IF count([Review Response #]) = 1 THEN 1 ELSE 0 END }

                           

                          Capture.PNG

                          If you bring in the design# on the view, it is basically giving it a one at a level of designer name and a design # if the response view #  equals a 1. Removing the design # from the view will give you your expected results.

                           

                          Capture2.PNG

                          1 of 1 people found this helpful
                          • 10. Re: Aggregation delemma
                            Rody Zakovich

                            Ok now I understand what you need.

                             

                            Please refer to Pooja's method above.

                             

                            Your question wasn't very clear, but I'm glad that is was able to be talked out so others could come in and help.

                             

                            Thanks Pooja Gandhi for hopping in.

                             

                            Rody

                            1 of 1 people found this helpful
                            • 11. Re: Aggregation delemma
                              Jonathan Mills

                              Thanks everyone, this was driving me crazy for the past day.  I need to brush up on my LoD skillz!