4 Replies Latest reply on Aug 21, 2018 9:32 AM by Terese Prascieniute

# Conditional aggregation or an aggregation on condition?

Hey guys,

I find it hard to undersatnd what Tableau is 'thinking' when I create these too calculations, which to my head should return the same result.

Here's my data:

CountryMonth
Metric
A2017-11-01
A2017-12-01
A2017-12-011
A2018-01-01
A2018-02-01
A2018-03-01
A2018-03-011
A2018-04-01
A2018-04-011
A2018-05-01
A2018-05-011
A2018-06-01
A2018-06-011
A2018-07-01
A2018-07-011

Now I have two calculations:

1)

IF max([Month]) = max({max([Month])})

then sum([Metric])

END

2)

sum(IF [Month] = {max([Month])}

then [Metric]

else null

end)

When I drag a single calculated field to the sheet, I get 6 if it is the first calculation, and 1 if it is the second calculation. I cannot comprehend why the two calculations should return different results.

Thanks for any help!

Terese

• ###### 1. Re: Conditional aggregation or an aggregation on condition?

Hi, Terese

Please see my explain below (I made a bit of data modification to show a better case for understanding) Hope this helps

ZZ

3 of 3 people found this helpful
• ###### 2. Re: Conditional aggregation or an aggregation on condition?

Terese,

In your first calcs, the condition is max([Month]) = max({max([Month])}), this will always return true no matter which month it is (because in the left side, it is 2018-07-01, in the right side, it is also 2018-07-01. This condition is just like if 1=1, then....), so when sum  6 records. it will be 6.

In your second calcs, the logic function "if then" will always return 1 since only 2018-07-01 is accordance with the calculation, when sum it, also return 1, because SUM(1)=1.

Hope it helps.

Michael Ye

6 of 6 people found this helpful
• ###### 3. Re: Conditional aggregation or an aggregation on condition?

Terese, this is a fun one.

If we express these 2 calculations in English, I think you'll see the differences.

1) If the highest value in the "Month" column is the same as the the highest value of the highest value of the "Month" column, the SUM all the values in the "Metric" column.

2) Find the row(s) in the table where the value in the "Month" column is equal to the highest value in the "Month" column, and SUM the values in the "Metric" column for those rows only.

In the first calculation, you're just saying that, as long as the 2 MAX values are equivalent, then SUM the "Metric" column.  It is impossible for the result of the IF statement to be anything but TRUE.

In the second calculation, you're basically filtering the table to a subset of rows based on a condition, and then SUMming the "Metric" values for the subset of rows you found.

3 of 3 people found this helpful
• ###### 4. Re: Conditional aggregation or an aggregation on condition?

Thanks Michael!

Plain English was what I needed.