5 Replies Latest reply on Mar 19, 2015 10:02 AM by pthivierge Branched from an earlier discussion.

    How to replace values [-11059 No Good data for Calculation] using PI Datalink in Excel

    samirmohanty

      I am getting the message ''[-11059 No Good data for Calculation] in my PI Excel sheet. I am using Calculated data formula, please suggest how i can replace this with BLANK or Zero as value.

        • Re: How to replace values [-11059 No Good data for Calculation] using PI Datalink in Excel
          dng

          Hi Samir,

           

          This message usually means that the underlying data value could not be evaluated based on the expression specified during the evaluation time period. I would suggest investigating the underlying data value and resolving the issue by fixing the expression or calculation logic. If you can provide more information about your calculation expression as well as the integrity of your source data, we will be able to further assist you.

          • Re: How to replace values [-11059 No Good data for Calculation] using PI Datalink in Excel
            pthivierge

            Hello Samir,

             

            I have branched this discussion as a new question as this is a complete different question than your initial previous question: Datalink link update on excel open

            This is another good question and I believe it is good that it stands on his own.

             

            Hope you agree

            • Re: How to replace values [-11059 No Good data for Calculation] using PI Datalink in Excel
              pthivierge

              Background

              The error [-11059] No Good Data For Calculation may occur in several circumstances:

              • Incorrect referrence to StartTime or EndTime, and point to a period where there is no data in the archive
              • Incorrect date format ( missing space, wrong format-> PI DATALINK ->settings->time format)
              • No data in archive for the calculation source tag
              • Only Digital states are present in the archive like "I/O Timeout"
              • Connection to the wrong PI Server ( this is in case you have several PI Servers available)
              • PI Point Security, it could be that the user has no read access on the PI tag

               

              Now we know why this error can occur, I would just like to add one more thing about the reason of the existence of this type of messages ( there are many others like "Tag Not Found" etc... ) :

              Datalink provide these messages to help the user determine why the function does not return the expected data, and this is why you cannot "easily" remove it.  If you really need to master entirely the messages returned when you query Data Against PI you may be better to create your custom code ( VBA + COM Add In, .NET Excel Add In,COM Wrapper for AF SDK for use with VBA , etc  )

               

               

              Now let's talk about possible solutions

              The is an existing discussion that address the same subject on PI Square: Datalink Error [-11059]

              Han Yong Lee has proposed two possible options, and one of them is the same as Eugene Lee's one:

              1. Use DataLink to output result to one set of cells (probably hidden) and use Excel IF function or VBA to look at the result and put into a desired format into a second set of cells.
              2. Combine Excel IF function with DataLink function, like:
                =IF(PIAdvCalcDat("CDT158", "*-2h","*","30m","average","time-weighted",0,1,0,"DEVPI")="[-11059] No Good Data for Calculation",
                "Calculation Error",
                PIAdvCalcDat("CDT158", "*-2h","*","30m","average","time-weighted",0,1,0,"DEVPI"))

              I like the 1st option better because the 2nd option doesn't allow you to modify the function or resize the DataLink function output easily

               

              The existing discussion ends with recommending to create an Excel Function to test the value(s), this is the best option :

              Example to replace the value by 0 if this is text

              =IF(ISTEXT(B1),0,B1)