10 Replies Latest reply on Jan 11, 2016 11:08 AM by Joe Oppelt

# Another Calculated Field Question

Hi All,

I'm hoping you can help me figure out a few calculated fields. My issue is that I have 57 records, but only 43 unique records. 14 records affected multiple LOBs.

What the numbers come down to are:

• There are 57 records total
• There are 43 unique records
• 12 of them are BIE (red)
• 31 of them are BII (blue)

So what I want in the tool tip (using FTS, the second line) as the example, is:

FTS Details:

Total for LOB: 12 (this calculation I have)

<LOB BIE> out of <Total BIE> - or - 2 out of 12

<LOB BII> out of <Total BII> - or - 10 out of 31

I can't figure out any of those 4 calculated fields.

Workbook is attached. Any help will be greatly appreciated.

Thanks

• ###### 1. Re: Another Calculated Field Question

Howdy Chrissy,

If I am correct you wanted to get a value that is the sum of the entire row in this case, the alias FTS in the LOB Field, which would be 12. It's divided into Impact Type which is 10 BII and 2 BIE

In order to get the 'Total BII' use this:

{EXCLUDE LOB :SUM(IF [Impact Type] = 'BII' THEN 1 ELSE 0 END)}

OR

{FIXED [Impact Type] :SUM(IF [Impact Type] = 'BII' THEN 1 ELSE 0 END)}

AND

{EXCLUDE LOB :SUM(IF [Impact Type] = 'BIE' THEN 1 ELSE 0 END)}

OR

{FIXED [Impact Type] :SUM(IF [Impact Type] = 'BIE' THEN 1 ELSE 0 END)}

Using fixed will make it always return the # of impact type with respect to if its BIE or BII while using EXCLUDE only removes LOB from the calculation. They both work in the current use case. You then just have to make your tool tip formatted accordingly with the right calculations showing in the view.

If I can be of more assistance please let me know.

Cheers!

Carl Slifer

InterWorks

• ###### 2. Re: Another Calculated Field Question

Here's an example using table calcs without using LOD.

1 of 1 people found this helpful
• ###### 3. Re: Another Calculated Field Question

100 ways to skin the cat.

• ###### 4. Re: Another Calculated Field Question

Hi Joe,

How would I make the total tickets calculation exclude duplicates. You can see in the table below that there are several duplicates. I'm trying to get the total ticket count to only count each Call_Number once. Further, the LOB BIE Total and LOB BII Total would need to count each Call_Number once. Hope that makes sense...

 Call_Number Impact Type 2627578 BII 2591876 BIE 2591876 BIE 2591491 BIE 2591491 BIE 2594370 BII 2594725 BII 2596320 BIE 2596320 BIE 2598178 BII 2599772 BIE 2599772 BIE 2599782 BII 2600692 BIE 2600692 BIE 2602356 BII 2595497 BII 2595707 BII 2608597 BII 2607763 BII 2609277 BII 2613715 BIE 2613715 BIE 2604478 BIE 2604478 BIE 2614473 BII 2614313 BII 2596802 BII 2621118 BII 2621018 BII 2620792 BII 2620195 BII 2604363 BII 2620483 BII 2609144 BII 2624259 BII 2624393 BII 2619985 BIE 2619985 BIE 2615822 BII 2616099 BII 2599846 BIE 2599846 BIE 2599846 BIE 2622960 BII 2606809 BIE 2606809 BIE 2606809 BIE 2624449 BII 2622754 BIE 2622754 BIE 2620414 BIE 2620414 BIE 2625119 BII
• ###### 5. Re: Another Calculated Field Question

Hi Carl, I can't get your attachment to open because it was created in a new version of Tableau than the version I'm using. Is there a way to save it so that i'll be able to open it?

• ###### 6. Re: Another Calculated Field Question

I just did SUM([Ticket]) because that's what was on the sheet.

You'll want to do a COUNTD of whatever identifies uniqueness.  So In [total tickets] you would do countd([call_number]), and likewise in [total BII tickets].  (The other one is just a subtraction ot the first two, so nothing to change there.)

Whether you use LOD like Carl did,  or table calcs like I did, the key is understanding how to tell Tableau how to "walk" through the table to count what you want to count (or add, or average, etc.)

In my example I got the totals for screen by telling tableau to cycle through all the LOBs and all the Impact types.  Don't restart.  Just count it all.  And I told tableau to get the totals for each LOB by cycling through the LOBs and the Impact Types, but restart the summing with each LOB.  Now, with all those table calcs avaliable to you, you can move them around any way you want in the tooltips.  (And/or place them on the labels of the bars!)

• ###### 7. Re: Another Calculated Field Question

Thanks Joe,

Bare with me just a bit longer if you can... I got the total tickets to work like this: { FIXED  : COUNTD([Call Number])}

The trouble I'm having now is trying to get the BIE Total. When I use this:

IF [Impact Type] = 'Business Impact External' THEN COUNTD([Call Number]) END

I get an error message that says "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions. Is just not something I can do or is the formula written incorrectly?

Thanks again.

• ###### 8. Re: Another Calculated Field Question

Do it this way:

countd(IF [Impact Type] = 'Business Impact External' THEN([Call Number]) END)

Tableau doesn't want you to do an aggregation as the result of an IF statement.  Instead, aggregate the the results.

• ###### 9. Re: Another Calculated Field Question

Thanks, Joe! That got me where I needed to go. I just wrapped that all up in FIXED { } and now I'm set. I appreciate your help.

• ###### 10. Re: Another Calculated Field Question

So you are saying you FIXED{} it!