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

# Aggregation delemma

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

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 #])

Hope this helps.

Regards,

Rody

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

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

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

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

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 #] )

Rody

• ###### 5. Re: Aggregation delemma

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

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??

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

Am I understanding what you want?

Rody

• ###### 7. Re: Aggregation delemma

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

Hi Jonathan,

Is this what you're looking for?

I've attached my workbook.

Ben

• ###### 9. Re: Aggregation delemma

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

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.

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

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

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