CODEWITHSUNDEEP
tableau-api
gemmo
gemmo

Reputation: 1456

Annualize data - Tableau

I'm trying to annualise my data in tableau, but get an error in the Calculated Field. "Cannot mix aggregate and non-aggregate arguments to function"

my formula is

 sum(profit)/month(selected date) *12

How do I get an integer for the current month? That seems to be the problem, it tries to aggregate the month as well.

Thanks.

Upvotes: 0

Views: 2767

Answers (1)

Alex Blakemore
Alex Blakemore

Reputation: 11919

Short answer: wrap the call to month in a call to min() -- which works well if you have MONTH([selected date]) on the visualization as a dimension.

There are three types of calculated fields in Tableau:

  • row level calculations which act on a single data row. They can read from values of other fields in the same row and return a single value per row.
  • aggregate calculations which act on a partition or block of data rows. They can reference the result of aggregating the values for a field across the entire partition, using a an aggregate function like SUM() or MIN().
  • table calculations which act on an entire table of aggregated results.

You can't mix and match. Everything in a calculated field must be all at one level or another -- either all referenced fields must use aggregation functions (for aggregate calculated fields) or no referenced fields must use aggregation functions (for data row level calculated fields).

Hence the error message you saw.

Sometimes you know that all values for a field will be the same in a partition based on your visualization, so the aggregation function seems unnecessary. But Tableau still requires you to be explicit about how to turn a block of values into a single value, because the calculation must be defined even when the visualization is partitioned differently. In these cases, you can use min(), max(), avg(), or perhaps attr() because they all return the same value for a list of identical values.

The first two types are typically executed on the server (i.e. they are implemented by Tableau emitting SQL to send to the database server). Table calculations are executed by Tableau on the client site to post-process the results from the database server.

Table calcs are the most complicated type, but can be very useful. Explaining them is a post for another day.

Upvotes: 1

Related Questions