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

    Calculation Question

    Morgan Strong

      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
          Ryan Slagle

          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
            Morgan Strong

            Hi Ryan,

             

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

             

            Thanks,

            Morgan

            • 3. Re: Calculation Question
              Ryan Slagle

              Hey Morgan,

               

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

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

                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

                CoEnterprise | Blog

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

                  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
                    Morgan Strong

                    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
                      Ryan Slagle

                      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
                        Lisa Li

                        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.

                        Count of Fails and Passes.PNG

                         

                        -Lisa

                        CoEnterprise | Blog

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

                          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).

                          LOD.PNG

                          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

                          CoEnterprise | Blog

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

                            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
                              Lisa Li

                              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

                              CoEnterprise | Blog

                              • 12. Re: Calculation Question
                                Morgan Strong

                                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
                                  Morgan Strong

                                  I should have included this calculation as well:

                                   

                                  • 14. Re: Calculation Question
                                    Lisa Li

                                    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

                                    CoEnterprise | Home

                                    1 2 Previous Next