1 2 Previous Next 16 Replies Latest reply on Oct 12, 2016 7:15 AM by Lisa Li

# Calculation Question

Hi,

I'm trying to see if there is a calculated field or something that can help me resolve a problem. The below image is just a cross tab of some data I have in a workbook, what I am trying to do is come up with some dummy field or calculation that says if ALL Source values = ALL CSS values then PASS, else FAIL for an individual Unique ID. I am trying to roll up the "Pass" or "Fail" indicator at the Unique ID level, not the individual record level.  For example, for both of the Unique ID's below these would FAIL because they have at least 1 record that fails.

I am working off of Tableau 9.1, and can attach a sample workbook if necessary.

Thanks!

Morgan

• ###### 1. Re: Calculation Question

Hi Morgan,

This should be pretty easy with a Table Calculation. Please attach a sample workbook and I can give it a shot.

• ###### 2. Re: Calculation Question

Hi Ryan,

Thanks for looking into it. Attaching a sample workbook here.

Thanks,

Morgan

• ###### 3. Re: Calculation Question

Hey Morgan,

It's quick and dirty but this worked for me:

1 of 1 people found this helpful
• ###### 4. Re: Calculation Question

Hey Morgan,

A quick solution I came up with is to create 2 calculated fields. First, find how many Source/CSS values there are for each ID:

{fixed [ID]: sum([Number of Records])}

Then, if Source Value = CSS Value, return 1 as a value and add all those values. If the added value = the total number of records for that ID, then ALL source values = ALL CSS Values and it passes.

if sum( { fixed [ID]: sum( if [Source Value]=[CSS Value] then 1

END)})= attr( [Calculation 1] ) then "PASS"

else "FAIL"

END

Hope this makes sense and works!

-Lisa

1 of 1 people found this helpful
• ###### 5. Re: Calculation Question

Hi Ryan,

Great that worked! Thanks so much for the help. Just for my own knowledge, what does the FIXED statement do in the logic? If it's too much too explain don't worry about it.

Thank you again!

Morgan

• ###### 6. Re: Calculation Question

Hey Lisa,

Thank you! This definitely works as well. I'm actually trying to go a step farther and COUNTD the number of Passes and Fails. So in my attached workbook example I would want a count of 2 fails and 1 pass. For some reason the calculations that I keep trying are returning 7 Fails and the rest Passes. Do you know why this may be?

Thanks!

Morgan

• ###### 7. Re: Calculation Question

I recommend reading up on LOD calcs, they're a bit tricky at first, but once you get the hang of them they've very powerful.

Overview: Level of Detail Expressions

• ###### 8. Re: Calculation Question

Hello again,

It's counting all of the individual fails and passes for each Target or Source Value. Depending on how you want to display or use the resulting count of fails and passes, you can create a calculation where:

countd( if [Calc] = "FAIL" then [Unique ID] END )

And also create one where [Calc] = "PASS".

You can then put both Measure Names and Values onto the sheet as text.

-Lisa

1 of 1 people found this helpful
• ###### 9. Re: Calculation Question

Here is how I learned LOD's:

{ fixed [SOME DIMENSION] : [some calc] }

In the case below, Fixed to State is { fixed [STATE] : sum([Sales]) } Notice how sum([Sales]) is the same for all of Alabama. This is because you're specifying at what level you want to find the sum([Sales]). If you take the time to add all the Sales amounts from Alabama, it'll add up to \$19,511.

Fixed to Category on the other hand is { fixed [CATEGORY] : sum([Sales]) } Notice how the value for Furniture is the same in Alabama and Arizona. This calc is finding the sum([Sales]) for ALL the different types of furniture (and the other categories) regardless of how the chart is set up and how it automatically slices the data (as seen in the reference [Sales] column).

This is incredibly useful for when you want to compare a graph to a benchmark or a fixed value. For example, if you wanted to compare Alabama's sales to the avg sales across all the different states, you can write an LOD calc.

- Lisa

1 of 1 people found this helpful
• ###### 10. Re: Calculation Question

Hey Lisa,

I'm trying your calculated field, but I'm returning an error for some reason?

I've tried with both of the previous calculations you suggested, but still not working. I tried my own calc, but that's not returning the correct value either?

I'm sure I'm missing something very small in my calculations??

Thanks!

Morgan

• ###### 11. Re: Calculation Question

Hey Morgan,

Can you show me what [Count - Pass/Fail Aggregation] is? I think the problem is it's a count, and therefore and aggregation of the pass/fail instead of the actual pass/fail results for each Source or Target Value.

I believe if you use either the calc from Ryan or me, instead of that measure, it'll work.

-Lisa

• ###### 12. Re: Calculation Question

Hey Lisa,

I used the calc you provided previously for that:

Let me know if I was supposed to use something else.

Thanks!

• ###### 13. Re: Calculation Question

I should have included this calculation as well:

• ###### 14. Re: Calculation Question

Hey Morgan!

Sorry, when I made the calc counting the #of passes and fails, I used Ryan's calc (LOD Checking Pass/Fail):

If [Status]="PASS" AND

{fixed [Unique ID]: countd([Status])}=1 then "PASS" else "FAIL"

END

This way you don't mix aggregate and non aggregate values in the count for # of pass/fail:

countd(if [LOD checking Pass/Fail] = "FAIL" then [Unique ID] END)

Sorry for the confusion!

-Lisa

1 2 Previous Next