9 Replies Latest reply on Dec 15, 2016 9:09 AM by Barbara J Otto

    Summarizing a calculated pass/fail formula

    Barbara J Otto

      Hey everyone, I feel like there may be a simple answer to this problem but I'm pretty new to Tableau and I'm not sure where to go. From an orders data source, within Tableau I'm calculating how many days between the order date and ship date. I am then comparing that calculated number of days to the Min and Max targets to determine pass or fail. I then want to summarize that at the model number level. I think I need to calculate a count of pass/fail at the order number level but I "can't aggregate an already aggregated field".

       

      Below is the code I'm using to calculate the pass/fail:

      IF MIN([Diff between Receipt and Ship]) > MIN([Max Lead Time])

      then "Fail"

      ELSEIF MIN([Diff between Receipt and Ship]) < MIN([Min Lead Time])

      then "Fail"

      ELSE "Pass"

      END

       

      As an example, for order 50 and 51 below. At an order number level my formula would calculate I would have 1 pass and 1 fail. However, when I remove the order number info it summarizes at Model Number  so it's comparing 28 calendar days to min target of 20 and max target of 40 and would show 1 pass at the model level. How can I get a count of the pass/fail at the order number level that can be summarized at any level (order number, model number, etc.)

       

       

            

      Order #Model #Order Receipt
        Date
      Order Ship DateCalc DaysMin TargetMax Target
      50AAAA1/1/20161/25/2016241020
      51AAAA1/1/20161/15/2016141020

       

       

      Any help would be greatly appreciated!

        • 1. Re: Summarizing a calculated pass/fail formula
          David Li

          Hi Barbara, can you share a packaged workbook? At least one of the fields in your calculation must already be aggregated, but I can't tell which one without seeing the workbook. My guess is that you just need to remove the MIN() around [Diff between Receipt and Ship], though.

          • 2. Re: Summarizing a calculated pass/fail formula
            Barbara J Otto

            I can't send a workbook with my actual data but I can try to create a report with some false data later today if I have time.

             

            The Calc Days field is calculated in Tableau. It is Order Ship Date - Order Receipt Date. In my code example it is titled [Diff between Receipt and Ship]. I had to include the MIN() in order to get the pass/fail code to work around the "aggregate" issue.

            • 3. Re: Summarizing a calculated pass/fail formula
              Walt Reed

              Hey Barbara,

              How is your Calc Days field calculated? I did a DATEDIFF function to calculate the days between receipt and ship:

              I created two parameters: one for min lead time and another for max lead time. Then I did a formula similar to yours for pass/fail:

              This is the output:

               

              Then, to do your count, do a FIXED LOD calculation so the numbers will change no matter how you slice and dice the information:

              Basic pass/fail 

              Chart Pass/Fail by Ship Mode

               

              Is this what you're aiming for?

               

              Walt

              • 4. Re: Summarizing a calculated pass/fail formula
                Ivan Young

                Hi Barbara,

                You can probably create a fixed LOD expression at the orderid level which will return a dimension providing the flexibility your looking for.  Hard to say exactly how without understanding your data.

                 

                 

                 

                 

                 

                • 5. Re: Summarizing a calculated pass/fail formula
                  Barbara J Otto

                  Thanks for the response guys!

                   

                  These seem like good solutions but I'm still getting some errors. The datediff function is working fine. However when I put it in the Pass/Fail calculation I still get the "aggregate" error unless I put MIN in front of the fields.

                   

                  And, with the now valid "pass/fail" code the count function is giving me the following error.

                   

                  • 6. Re: Summarizing a calculated pass/fail formula
                    Walt Reed

                    Hey Barbara,

                    Are you using a parameter for your Max and Min Lead Times? If you do, then you won't need to wrap the fields in the Pass/Fail calc in MIN. Plus, it allows greater flexibility if you want to change the lead times.

                     

                    You're getting the error because the Pass/Fail calc fields are aggregated (using the MIN function), which results in a Measures field. So when you try to do the final LOD calc, it won't work since LODs need a Dimension for the first part of the formula.

                     

                    Walt

                    • 7. Re: Summarizing a calculated pass/fail formula
                      Barbara J Otto

                      The min and max lead times are two separate fields coming in from the data source. Is there a way to include the Min and Max in the pass/fail calculation without turning them into Measures?

                       

                      As you can tell I'm pretty new at Tableau so I appreciate the help.

                      • 8. Re: Summarizing a calculated pass/fail formula
                        Walt Reed

                        Hey Barbara,

                        I would think that if they are coming in from the data source, you could use a formula like

                        IF [Weeks between Ord Rcpt and Final Plt Ship Date] > [LA Data (Lane Availability benchmark data)].[Max Lead Time] THEN 'Fail'

                        ELSEIF [Weeks between Ord Rcpt and Final Plt Ship Date] < [LA Data (Lane Availability benchmark data)].[Min Lead Time] THEN 'Fail'

                        ELSE 'Pass'

                        END

                         

                        If that doesn't work, is it possible for you to attach a packaged workbook that we could take a look at?

                         

                        Walt

                        • 9. Re: Summarizing a calculated pass/fail formula
                          Barbara J Otto

                          This is still giving me the "aggregate" error. I think I'll submit this to the Tableau support team and see if they can walk me through it. Once I get an answer I will update this with the solution they came up with.

                           

                          Thanks for your responses it has been really helpful!