5 Replies Latest reply on Sep 6, 2011 6:52 AM by Joe Mako

# Using Calculated Field to filter data

I have some data from a sales ledger in which some lines indicate a unique purchase, and some indicate a secondary purchase.

Whether a purchase is unique or not depends on the Product Group.  If it belongs to groups 1, 3, 4 or 5 then it is a unique purchase, if it belongs to groups 2, 6, 7 or 8 then it is a secondary purchase.

My data has columns of:

Date Stamp | Customer ID | Product | Quantity | Unit Price | Total Amount | Product Group

I think that I need to write a calculated field that I can call "# Unique Purchasers" that says:

If Product Group is 1, 3, 4 or 5 then return the quantity, otherwise return 0.

I am trying to calculate a Yield, which would them be Total Income / # Unique Purchasers.

Am I going about this the right way?  If so, how would I form that calculated field?

I have created a Group that splits the Product Groups into Unique Purchases and Secondary Purchases, and I would ideally prefer to use that to avoid having to re-write the calculated field for additional Product Groups.

Thank you!

• ###### 1. Re: Using Calculated Field to filter data

Drew, it would be great if you could include 20 or so lines of data to work with.

• ###### 2. Re: Using Calculated Field to filter data

Product Group Type

```CASE [Product Group]

WHEN 1 THEN "Unique"
WHEN 2 THEN "Secondary"
WHEN 3 THEN "Unique"
WHEN 4 THEN "Unique"
WHEN 5 THEN "Unique"
WHEN 6 THEN "Secondary"
WHEN 7 THEN "Secondary"
WHEN 8 THEN "Secondary"
END
```

This way you have it as a calculated field, which is more versatile than an ad hoc group.

Then you can have calculated fields like:

Unique Customer ID

```IF [Product Group Type]="Unique"

THEN [Customer ID] END
```

and

Yield

`SUM([Total Amount])/COUNTD([Unique Customer ID])`

In the attached you can see that there are 5 "Unique Customer ID", with an overall Total amount of 457. So the Yield is 457 / 5 = 91.4

Depending on your data and business logic, you might want a COUNT() instead of a COUNTD() in the Yield formula.

• ###### 3. Re: Using Calculated Field to filter data

Thanks Joe, I did that and it worked great.

• ###### 4. Re: Using Calculated Field to filter data

Thanks, helped me too!

So just for my info, should I stop using ad-hoc group and start creating my group using rules?  Sometimes it is harder when you have a long list of elements, is it something plan: to be able to use ad-hoc group in calculated field?

Thanks,

JF

• ###### 5. Re: Using Calculated Field to filter data

It is something Tableau is aware of, but there is no word yet on when ad hoc groups will be able to be referenced in calculated fields.