6 Replies Latest reply on Mar 25, 2016 1:54 PM by Kenneth Young

    Calculated Field isnt valid or gives error from source

    Kenneth Young

      I tried looking through the forum, but couldn't find anything that specifically helped with my problem... I am using Smart Sheet as my Data Source. I am trying to create a calculated field and cant get it to work as a single CF. The goal is to have "If today is past project XX due date then flag as past due, if the project does not have a completed status." I couldnt figure out a code for "does not."

      So my workaround was to say, if the status = the choices available other than complete, flag as past due. But the calculations wont work. I have tried creating several calculations for this, and none would work. Some were not valid, and others come back with a syntax error. 1 example of a valid but syntax error is;

       

      IF TODAY()> [CA/PA Due Date] AND [CAPA Status]= "Initiated (Collect Evidence)" OR  [CAPA Status]= "RCA Team (Analyze)" OR  [CAPA Status]= "CAPA Tracking (Countermeasures)" THEN 'Past Due'

      END

       

      What I had to do was create 3 separate fields that look like;

       

      IF [CA/PA Due Date]> [Actual CA/PA Completion Date] AND [CAPA Status]= "Initiated (Collect Evidence)" THEN 'Past Due'

      END

       

      All 3 fields appear to have accomplished what I wanted. However, I want to know what I did wrong so the first calculation wouldnt work. And how to fix the calculation so that when I try to do something similar in the future I can just create 1 field instead of multiple.

       

      Thanks for any help someone can give me in this.

        • 1. Re: Calculated Field isnt valid or gives error from source
          Esther Aller

          Hi Kenneth,

           

          It's always a little difficult to know exactly what's going on with a calculation without seeing the data too. The syntax of the first calculation looks like it should be valid. For comparison I made the following valid calculation using the sample data set Superstore:

           

          IF TODAY() > [Order Date]

          AND [Region] = 'Central' OR [Region] = 'West'

          THEN 'Past Due'

          END

           

          If you can share a sample workbook with the calculation that is not valid, or even just the exact error message, then I might be able to give you some more insight.

           

          As a side note, "not" can be either != or <>. For example [Region] != 'South' is region not equal to south. Check out the Product Help entry Operators for more information.

           

          I hope this helps

          • 2. Re: Calculated Field isnt valid or gives error from source
            Kenneth Young

            Thanks for the reply. The Product Help Resource will be very helpful in the future.

             

            I have been trying to mess around with it more and am not having any luck. I have gotten several calculations that dont use a date dimension to work. When I add a non calculated dimension that is a date, the date shows up just fine. But when I try to incorporate the date dimension into my calculation, it always comes back with a Null value (which I believe wont trigger my desired effect to change the color.) or I receive the error message below.

             

            Here is the Error I am receiving. I tried creating a simpler calculation in the hopes that would work. But same results.

             

            Calculation:

            IF TODAY()> [CA/PA Due Date] AND [CAPA Status]!= 'Complete (Verify)' THEN 'Red'

            END

             

            There was an error executing an SQL query: 'near "(": syntax error' (1)

             

            Here is the Query:

            SELECT (CASE WHEN (({fn CURRENT_DATE()} > "Medford_CAPA__Tracking_and_Verification_Log__HCAAAGEHDDHHGGIs__Home_"."CA/PA Due Date") AND ("Medford_CAPA__Tracking_and_Verification_Log__HCAAAGEHDDHHGGIs__Home_"."CAPA Status" <> 'Complete (Verify)')) THEN 'Red' ELSE NULL END) AS "Calculation_EBFHDIGAFHFHFBHIDH",

              "Medford_CAPA__Tracking_and_Verification_Log__HCAAAGEHDDHHGGIs__Home_"."FS CAPA Number" AS "FS_CAPA_Number"

            FROM "Medford CAPA- Tracking and Verification Log__720006473377668s_(Home)" "Medford_CAPA__Tracking_and_Verification_Log__HCAAAGEHDDHHGGIs__Home_"

            GROUP BY 1,

              2

             

            Just to recap, if my due date is past, and my status is not complete, then I want the color to be red.

             

            Thanks again for your help with this.

            • 3. Re: Calculated Field isnt valid or gives error from source
              Kenneth Young

              I forgot to add the workbook. Here it is.

               

              The sheet is Due Date/Late

              The Dimension is CAPA Late Red

               

              Thank you again.

              • 4. Re: Calculated Field isnt valid or gives error from source
                Esther Aller

                Hey Kenneth,

                 

                This is great information! I am unable to view the visualization in your workbook because the data source has not been extracted. Check out Preparing Workbooks for Sharing for exact steps on saving a workbook to be shared.

                 

                I did notice that the calculated field [CAPA Late Red] is giving the error "Unknown function TODAY called". This might actually be an issue with the database not supporting TODAY(). What data source are you connecting to?

                 

                Do you have data in the future? If not, then you should be able to remove the "TODAY() > [CA/PA Due Date]" conditional statement from your calculation and have it work as intended.

                 

                 

                • 5. Re: Calculated Field isnt valid or gives error from source
                  Kenneth Young

                  Thanks for the extraction directions. Hopefully the data visuals will show up now.

                   

                  I do have dates in the future. Those are my due dates for CAPA completion. Your suggestion of removing the TODAY() from the calculation does work to get my colors to change. I have created as calculation 1. It just doesnt have that date filter for it that says, I've got two weeks left to complete this CAPA so dont turn Red yet.

                   

                  My Data Source is a Smart Sheet. So I am starting to think there is a support issue from that database.

                   

                  Thanks again for your help. Even though I still have the problem I have learned plenty.