9 Replies Latest reply on Jan 27, 2017 8:38 AM by Dustin Brickwood

# How to create a calculated field from within a single column

Hello All!

--Using Version 9.3

The title of this discussion is brutal but I do not know a better way of describing my issue that I am trying to resolve. Basically, I have a field [Sales Type] that has six rows (see below):

I want to be able to create a calculated field, that would look something like this: [Event Sales - Return of Event Sales], ( I would do this for each, Event, Discount, Regular).  Then take each of these fields and display each calculated field within a table. I have attached a packaged workbook that illustrates my point a bit clearer. The first sheet shows the [Sales Type] field as is, while the second sheet shows what I would like the end result to be.

Any suggestions are welcomed and encouraged! Also, if you happen to think of a better name to describe this post please let me know so I may change the title for the next person looking to resolve a similar issue.

Thanks,

Dustin

Sales Type

 Event Sales Discount Sales Regular Sales Return of Event Sales Return of Discount Sales Return of Regular Sales
• ###### 1. Re: How to create a calculated field from within a single column

Dustin,

In the simplest case, your calculated fields would look like this:

[Event Sales - Return of Event Sales]

```SUM( IF [Sale Type1] = 'Event Sales' THEN [Amount] END )
-
SUM( IF [Sale Type1] = 'Return of Event Sales' THEN [Amount] END )
```

This simple aggregation calculation will automatically honor any filters/segments you apply to your view.

• ###### 2. Re: How to create a calculated field from within a single column

Hi Jamieson,

I should have specified that in my actual workbook the amount field is an aggregated calculations SUM(sales quantity * unit price) so the calculation you have provided me indicates that I cannot mix aggregate and non aggregate comparisons or results in an IF expression.

Sales Type is a string as well.

• ###### 3. Re: How to create a calculated field from within a single column

Dustin,

Seems like your calculation could be done at the row level, and doesn't need to be an aggregation. What if you rewrite to it be simply:

```[Sales Quantity] * [Unit Price]
```

Then does the it work in the context of the calculation I provided above?

• ###### 4. Re: How to create a calculated field from within a single column

Hi Jamieson,

Yes this has worked. However, I have to change the  - operator to a + in order for to actually subtract?

SUM( IF [Sales Type] = 'Event Sale' THEN [Sales Amount] END )

SUM( IF [Sales Type] = 'Return of Event Sale Item' THEN [Sales Amount] END )

This actually subtracts correctly. Weird but hey it works.

Thanks,

Dustin

• ###### 5. Re: How to create a calculated field from within a single column

It gets messy if you have a different calculation for each Sales Type. Each calculation would look something like this:

Discount Sales Initial

IF [Sales Type] = "Discount Sales" THEN [Amount] END

Discount Sales Return

IF [Sales Type] = "Return of Discount Sales" THEN [Amount] END

If you had these written out for each Sales Type, you could do 3 different calculations that follow this convention:

Final Discount Sales

[Discount Sales Initial]-[Discount Sales Return]

I think what you really need here though is a data restructure. Think of 3 rows being Event Sales, Discount Sales, and Regular Sales. The columns would be Sales Type, Amount, and Return Amount. The data is easier to interpret, and Tableau can whip up a visualization like nothing.  Plus this allows for future expansion into new sales types as well. Perhaps they begin tracking Membership Sales, for example. The correct data structure would allow for easy management, rather than having to write a whole bunch of additional calculations. If you can't restructure your data for some reason, is there any other identifying fields you're not mentioning? Anything that could serve as an ID field on which to pivot your data?

• ###### 6. Re: How to create a calculated field from within a single column

Dustin,

Ah, it sounds like your return line items are already negative. I didn't have visibility on the original data, so I made the assumption everything was absolute values.

• ###### 7. Re: How to create a calculated field from within a single column

Hi Vincent,

Yes, there is an ID field that identifies each sales type.

For instance 15 = Event Sales

ect.

How would I be able to use this to pivot the data because I agree it sure would be nice to have it structured as you had mentioned.

• ###### 8. Re: How to create a calculated field from within a single column

Do the ID fields match for "Regular Sales" and "Return of Regular Sales"?

• ###### 9. Re: How to create a calculated field from within a single column

No each are given its own ID #

Regular Sale  = 12

Return of Regular Sale = 13

ect.