1 2 Previous Next 15 Replies Latest reply on Oct 4, 2017 10:15 AM by Kim Cataldo

# Mixing aggregate and non-aggregate IF statement - returns null

Looking for input to fix the following formula:

IF attr([Segment]) = "Consumer" THEN [Profit Ratio] ELSE 0 END

Where Profit Ratio is an aggregated measure (SUM([Profit])/SUM([Sales]))

The goal is to create an IF statement that returns the profit ratio for only the Consumer segment.  The formula above follows Tableau's solution for mixing aggregate and non-aggregate arguments within an if statement. (http://kb.tableau.com/articles/knowledgebase/calculation-error-resolving-aggregate-nonaggregate-arguments)

The formula is valid however, it only works when the Segment dimension is present within the view, otherwise it returns a null.  (See attached workbook for example.)

Thank you for any insight you can provide!

• ###### 1. Re: Mixing aggregate and non-aggregate IF statement - returns null

Hey Kim,

I like to take a different approach and move the "IF" login inside of the SUM.

Something like this:

SUM(IF [Segment] == 'Consumer' THEN [Profit] END)

• ###### 2. Re: Mixing aggregate and non-aggregate IF statement - returns null

Kim,

If you dont want to see Segment in the view, can you just bring it to filter box and click only Consumer and you still get it the result you want as seen below:

• ###### 3. Re: Mixing aggregate and non-aggregate IF statement - returns null

Hi Derrick - thank you for your response.  Unfortunately the formula 'SUM(IF [Segment] == 'Consumer' THEN [Profit] END)' returns the wrong result.  It looks like it is adding together all of the profit rations (134,119) instead of providing the aggregated result (12%)

• ###### 4. Re: Mixing aggregate and non-aggregate IF statement - returns null

Hi Sherzodbek - thanks for your response.  Ultimately I am trying to create a dual axis view where I can compare the profit ratios of two different Segments within the same view.  Filtering won't work for this approach.

• ###### 5. Re: Mixing aggregate and non-aggregate IF statement - returns null

Kim -- Derrick's example wasn't intended to be a solution.  It was an example to show how to embed "IF" logic inside the parentheses.  He was suggesting that maybe something like that would serve your purposes.

• ###### 6. Re: Mixing aggregate and non-aggregate IF statement - returns null

If you are going to process [Segments] individually  (as you are doing in your calc), you have to have the table partitioned to the [Segment] level.

One thing you can do is put [Segment] on the detail shelf instead of on rows or columns.  See the first sheet in the attached.

1 of 1 people found this helpful
• ###### 7. Re: Mixing aggregate and non-aggregate IF statement - returns null

Hi Joe - Keeping my original formula (IF attr([Segment]) = "Consumer" THEN [Profit Ratio] ELSE 0 END) but putting Segment on the detail shelf did the trick.

What threw me initially is that when the same IF statement is used using an non-aggregated measure (for example 'IF [Segment] = "Consumer" THEN [Sales] ELSE 0 END') then it is not necessary to have the dimension on the detail shelf.  Having this solution when the measure is aggregated is very helpful.  Thanks for your help!

• ###### 8. Re: Mixing aggregate and non-aggregate IF statement - returns null

Right.  If you do non-aggregated calcs, it's done at the row level -- row by row, no matter what the underlying table looks like.  But when you have aggregates, things are aggregated based on the way the table is shaped.

An easy way to conceptualize that is when you have a date field on ROWS, and you have SUM(Sales) in the sheet.  If you have the date expanded all the way out to the DAY(date), then you get sales summed up by day.  The sheet is "shaped" on a day by day basis, so sales are summed up day by day.  But if you collapse the date down to MONTH(date), the shape is month by month, and sales are summed month by month.  And if you tried to show DAY(date) in the tooltips on the monthly sheet, you can expect that it would not work for you because you don't have it shaped down to the day.

Likewise, without [Segment] somewhere on the sheet, Tableau doesn't have visibility to the segment level to do the aggregate calc for you.

• ###### 9. Re: Mixing aggregate and non-aggregate IF statement - returns null

Here's something else to consider.

You could have had row-level calcs do this:

Consumer sales:

IF [Segment] = "Consumer" THEN [Sales] END

Consumer profits:

IF [Segment] = "Consumer" THEN [Profit] END

Those calcs would have values only if [Segment] = "Consumer" .  The rest of the rows have null.

Then, another calc would give you consumer ratio:

sum([Consumer profits])/sum([Consumer sales])

And it would work regardless of whether or not [Segment] is on the sheet.  Further, the two initial calcs could compare to a parameter value instead of a hard-coded "Consumer', allowing the user to look at any segment.
See Sheet 3 in the attached.  I didn't do the parameter exercise in there, but you can give that a try if you want.

1 of 1 people found this helpful

• ###### 11. Re: Mixing aggregate and non-aggregate IF statement - returns null

Hi Joe Oppelt - It's been a while since you answered my question but I have a follow-up I'm grappling with.  What if I want to create a calculation that divides the profit ratio for one segment compared to another?

For example: Corporate profit ratio / Consumer profit ratio.  I am looking to be able to show Corporate profit ratio is X times larger/smaller than consumer profit ratio.  I attached an example in the workbook.  Any help would be appreciated - thank you!

• ###### 12. Re: Mixing aggregate and non-aggregate IF statement - returns null

Did you do this in 10.4?  I haven't installed that yet.

The first thing I would have looked at is to follow the same principle I suggested in my previous reply from Oct 26, 2016 8:59 AM.

Isolate corporate numbers in one calc, and consumer numbers in another calc.  then do your aggregates, LODs, table calcs, whatever ... using those values you isolated.

• ###### 13. Re: Mixing aggregate and non-aggregate IF statement - returns null

Thanks for your quick response.  The calculation I'm trying to make work looks something like this:  (I realize sales wouldn't be calculated by a distinct count of an id number, but that's what my real data looks like.)

Consumer sales:

IF attr([Segment]) = "Consumer" THEN COUNTD([ID Number] END

Corporate sales:

IF attr([Segment]) = "Corporate" THEN COUNTD([ID Number] END

Ratio of Corporate sales to Consumer sales:

[Corporate sales]/[Consumer sales]

My assumption is that putting Segment on the detail shelf no longer works because the ratio calculation includes more than one segment.  Is there a workaround for this?  Thanks for your help!

• ###### 14. Re: Mixing aggregate and non-aggregate IF statement - returns null

If you do this with LOD calcs, you can get those values without putting segments on the sheet.

Something like this:

{ FIXED [Continent], [Region] : COUNTD( IF [Segment] = "Corporate" then [ID Number] END) }

(and do the same for Consumer.)

What you will get is a value for every combo of Continent and Region -- on every row -- that is the COUNTD of ID for segment=Corporate.  And another for Consumer.  You'll be able to use that everywhere, whether or not Segment is on the sheet.

1 of 1 people found this helpful
1 2 Previous Next