CODEWITHSUNDEEP
tableau-api
masteusz
masteusz

Reputation: 3621

Tableau: Create a table calculation that sums distinct string values (names) when condition is met

I am getting my data from denormalized table, where I keep names and actions (apart from other things). I want to create a calculated field that will return sum of workgroup names but only when there are more than five actions present in DB for given workgroup.

Here's how I have done it when I wanted to check if certain action has been registered for workgroup:

WINDOW_SUM(COUNTD(IF [action] = "ADD" THEN [workgroup_name] END))

When I try to do similar thing with count, I am getting "Cannot mix aggregate and non-aggregate arguments":

WINDOW_SUM(COUNTD(IF COUNT([Number of Records]) > 5 THEN [workgroup_name] END))

I know that there's problem with the IF clause, but don't know how to fix it.

How to change the IF to be valid? Maybe there's an easier way to do it, that I am missing?

EDIT: (after Inox's response)

I know that my problem is mixing aggregate with non-aggregate fields. I can't use filter to do it, because I want to use it later as a part of more complicated view - filtering would destroy the whole idea.

Upvotes: 1

Views: 28497

Answers (1)

Inox
Inox

Reputation: 2275

No, the problem is to mix aggregated arguments (e.g., sum, count) with non aggregate ones (e.g., any field directly). And that's what you're doing mixing COUNT([Number of Records]) with [workgroup_name]

If your goal is to know how many workgroup_name (unique) has more than 5 records (seems like that by the idea of your code), I think it's easier to filter then count.

So first you drag workgroup_name to Filter, go to tab conditions, select By field, Number of Records, Count, >, 5

This way you'll filter only the workgroup_name that has more than 5 records.

Now you can go with a simple COUNTD(workgroup_name)

EDIT: After clarification

Okay, than you need to add a marker that is fixed in your database. So table calculations won't help you.

By definition table calculation depends on the fields that are on the worksheet (and how you decide to use those fields to partition or address), and it's only calculated AFTER being called in a sheet. That way, each time you call the function it will recalculate, and for some analysis you may want to do, the fields you need to make the table calculation correct won't be there.

Same thing applies to aggregations (counts, sums,...), the aggregation depends, well, on the level of aggregation you have.

In this case it's better that you manipulate your data prior to connecting it to Tableau. I don't see a direct way (a single calculated field that would solve your problem). What can be done is to generate a db from Tableau (with the aggregation of number of records for each workgroup_name) then export it to csv or mdb and then reconnect it to Tableau. But if you can manipulate your database outside Tableau, it's usually a better solution

Upvotes: 3

Related Questions