9 Replies Latest reply on May 17, 2018 1:42 PM by Milton Forde

# Aggregate and Non-Aggregate Mix Calculation

Hello!

I'm trying to do a calculation that uses an aggregate and non aggregate mix.  I've looked at the past posts and I thought I figured it out by using the ATTR function, but when I did this the calculation became a measure and more importantly, I am getting Null, not seeing my breakouts.  Below is an example of what I am trying to do.

I feel I'm missing an additional step, but can't seem to figure it out and I'm hoping that someone will be able to help me out here.  Attached is my work book ver 10.1.  Segment Size is the calculation built which I need to be a dimension showing the break outs so I can see amount of dollars for each group.  Here is the internal calculation used.

IF  ([Dollars per Eq Vol] >= 0.781 AND ATTR([Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD")

)THEN "super premium > 0.781 "

else if ([Dollars per Eq Vol] >= 0.641 AND [Dollars per Eq Vol] <= 0.78  AND ATTR

([Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD")

)THEN "premium 0.641 - 0.78 "

else if ([Dollars per Eq Vol] >= 0.491 AND [Dollars per Eq Vol] <= 0.64 AND ATTR

([Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD")

)THEN "mainstream 0.491 - 0.64 "

else if ([Dollars per Eq Vol] < 0.49 AND ATTR

([Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD" OR [Sub-Category] = "POD")

)THEN "value < 0.49 "

END

END

END

END

-Milton

• ###### 1. Re: Aggregate and Non-Aggregate Mix Calculation

If there is any aggregate in the calc, it has to end up being a measure.

And if any one token in there is an aggregate, the EVERY reference to a fiend has to be an aggregate (if the field itself is not already an aggregate.)

Is  [Dollars per Eq Vol] an aggregate?  If not, then you have to aggregate it with SUM or MIN or ATTR, etc.

And maybe wrapping all those [Sub-Category] = "POD"  iterations on one ATTR() envelope works, but I would be doing ATTR([Sub-Category]) = "POD" individually on each one.

• ###### 2. Re: Aggregate and Non-Aggregate Mix Calculation

What exactly are you trying to compute? [Dollars per Eq Vol] is an aggregate that computes SUM([Dollars]) and SUM([Eq Vol]). But you want that summed over which records? All of them? Just one MKT, PER, or Sub-Category?

You may need to use a level of detail expression to tell Tableau how to compute [Dollars per Eq Vol] but it will depend on what you want.

Dan

• ###### 3. Re: Aggregate and Non-Aggregate Mix Calculation

Hi Dan!

Thank you for responding.  I would want my calculations to be summed over all records, so the user can then filter on a MKT, PER, etc to get at the breaks I'm trying to create.

-Milt

• ###### 4. Re: Aggregate and Non-Aggregate Mix Calculation

If you aggregate all records, there will only be one number and so only one of your "segments" will apply. Is that what you are expecting?

Dan

1 of 1 people found this helpful
• ###### 5. Re: Aggregate and Non-Aggregate Mix Calculation

I got it!

I reviewed what both you and Joe said, and it was a simple fix.  I needed to remove the Sum from my calculation.  In essence I did not need the calculation summed, which was causing my issue.  Once I removed it, I was able to get my break outs and the Null went away.

Thank you for the feedback as it helped me to figure this out.

Thanks again!

-Milt

• ###### 6. Re: Aggregate and Non-Aggregate Mix Calculation

I just opened up your workbook, and while I was looking at it you posted this.

Yes.  If you want that calc to be done for each row, then yank the SUMs out of it.

1 of 1 people found this helpful
• ###### 7. Re: Aggregate and Non-Aggregate Mix Calculation

Thanks again Joe.

I appreciate the inputs!!

-Milt