11 Replies Latest reply on Jul 25, 2018 8:08 AM by Mahfooj Khan

# Getting around Aggregation and Non Aggregation calculations

Hi All, I am new to tableau and slowly getting my head around it. I am developing a workbook and I am building out a calculation which I am struggling with.

Ultimately, I want to take the answer to the below and divide it by the CountD of the Opportunity ID.

{ FIXED [Contact Id 18],[Opportunity Id],[datetime]:SUM([Pageviews])}

If I do this in the calculation, then I rightly get a aggregation and non aggregation error message.

Is there a way to convert this calculation?

Matt

• ###### 1. Re: Getting around Aggregation and Non Aggregation calculations

MIN({ FIXED [Contact Id 18],[Opportunity Id],[datetime]:SUM([Pageviews])})

• ###### 2. Re: Getting around Aggregation and Non Aggregation calculations

Hi David, thanks for coming back to me on this. Unfortunately, I don't think that will work because it will bring back the min total of (in this case) page views. My problem is that the total i have is correct, it has just been exploded up (through linking the view to Salesforce) by the number of distinct Opp ID's. So I need a calculation that will keep the total the same but allow me to divide by the CountD of Opportunity IDs

• ###### 3. Re: Getting around Aggregation and Non Aggregation calculations

Hi Matt

First welcome to Tableau

LOD's are used to create an additional level in your data - they require that the expression that follows the colon be aggregated but the LOD itself is not and aggregate -

It can be used in any other type of calculation - nested LOD's , table calculation or as in your case a regular aggregation -

in words your expression says for each combination of contact id 18 , opportunity and datetime - sum the page views

so the answer to your question will depend on how you want to use it in the vis

eg if your are want to look at "contact id 18"  over all opportunities and date times then you would use sum() to add all the combinations together then divide by the count of the opportunities

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Getting around Aggregation and Non Aggregation calculations

Hi Matt,

I don't know will it work or not. However you may trt this

{ FIXED [Contact Id 18],[Opportunity Id],[datetime]:SUM([Pageviews])}/

{ FIXED [Contact Id 18],[datetime]:COUNTD([Opportunity Id])}

Let us know if this work else share some sample mock up data

Mahfooj

1 of 1 people found this helpful
• ###### 5. Re: Getting around Aggregation and Non Aggregation calculations

Hi Matt,

You are a bit wrong. You freeze this value using LOD. Min in this case doesn't affect. However it will help you to avoid mistake.

Welcome to Community.

D

• ###### 6. Re: Getting around Aggregation and Non Aggregation calculations

thanks Jim for the insight. Very Helpful for a beginner.

• ###### 7. Re: Getting around Aggregation and Non Aggregation calculations

Hi Mahfooj, that seems to have worked, so i appreciated the help! Cheers.

• ###### 8. Re: Getting around Aggregation and Non Aggregation calculations

Hi David, thanks for the insight and explanation. It is much appreciated. Kind regards.

• ###### 9. Re: Getting around Aggregation and Non Aggregation calculations

If you haven't done so already look into the Tableau on-line training  Tableau Training: View Training Courses

The entire course is structured but broken into segments and down further into topic specific videos - they are short 3-5 min typically -

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 10. Re: Getting around Aggregation and Non Aggregation calculations

Welcome, pls mark my response as correct and helpful to close this thread

• ###### 11. Re: Getting around Aggregation and Non Aggregation calculations

You're welcome