Create a calculated field for a filtered grouped data in Tableau

Question

I have a data set with columns corresponding to Country (Aus/NZ), Sent, Opened, Open Rate (=Opened/Sent) over the months.

I can calculate Sent & Open by summing up the values & Open Rate by diving those two.

Is it possible to create a calculated field Open Rate_Aus which holds the value of Open Rate for AU only.

I tried using expressions as below but in vain. It throws error saying 'can't mix aggregate & non-aggregate in IF expressions'

{FIXED [Country] : max(IF [Country] = 'AU' THEN [Open Rate] END)}

Answer 1

Why not just filter for AU ? Personally I don't like hard-coding values like that because you will then need a NZ version in the future and it just makes Tableau slow.

BUT, if you still need to, try this:

{FIXED [Country] : max(IF attr([Country]) = 'AU' THEN [Open Rate] END)}

Answer 2

No problem. But I'll hopefully answer your question and then give you another fun solution.

I'd use the formula...

IF [Country] = 'AU' THEN [Open Rate] END

And the Open Rate calculation is:

[Open] / [Sent]

The solution looks like this...

The alternative (more fun) solution would be to create a parameter based off your Country dimension.

Or just type the exact Countries in, so it looks like this before you click OK.

Then you can create a more dynamic Open Rate by the Chosen Country so users can choose.

Answer 3

One thing I wanted to add about avoiding the hardcoding of country name would be to maintain the list in a separate csv or Excel file and join it in your data source. This way you can add or remove countries without publishing the views.