5 Replies Latest reply on Sep 6, 2016 7:44 AM by Andrew Watson

Create A Calculated field

I have two rows of data that have the same unique identifier. Each row represents an action for a specific transaction. A simplified example could be as follows, with ID and Action as the dimensions.

Row1 = ID 123456789, Action SALE

Row2 = ID 123456789, Action SAVE

I need a count for every time both actions occur where the ID is the same. I need to be able to sum this count and subtract it from another calculated field & then use that result in another calculated field.

Example: Sum of Action "SALE" = 100, Count of ID that has both "SALE" and "SAVE" actions = 20  I want my result to be 80, which I then need to be able to divide into a total to calculate a conversion rate. So, if there where 100 sales, but 20 also contained a save I have 80 sales, into 100 calls = 80% conversion rate.

Anyone know of a way to do this? I am new to Tableau wit limited experience.

Thanks!

• 1. Re: Create A Calculated field

A simple solution, assuming an ID would only appear once for SALE without SAVE would be to do a count and all those with more than one appearance should be counted as part of your 20. For example you could try this:

IF {FIXED [ID]:Count([ID])} > 1 THEN [ID] END

If you do a COUNTD on the result of that calculation it should give you the 20 in your example.

• 2. Re: Create A Calculated field

Unfortunately there are other actions recorded under the same unique identifier such as Call, Refusal, Cancel and Result - I need to limit the count specifically to Save and Sale actions. Every transaction as at least 2 actions (Call & Result) therefore a Sale would have three actions under the one unique identifier.

• 3. Re: Create A Calculated field

Does this improve things?

IF {FIXED [ID]:Count(IF [Action] = 'SALE' OR [Action] = 'SAVE' THEN [ID] END)} > 1 THEN [ID] END

Alternatively can you just filter out all actions that aren't Sale or Save?

• 4. Re: Create A Calculated field

If I limit this worksheet to sale and save results only I can filter everything else out. When I attempt to use either formula above I get an error message that states: Cannot mix aggregate and scalar expressions. The unique identifier an 11 digit number but is stored as text in our db.

• 5. Re: Create A Calculated field

I don't know what causes that error in this case. If I put your dummy data into Tableau

 Id Action 123456789 SALE 123456789 SAVE

And use the suggested formula it works fine for me: 