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.
Expression is correct, but value is not coming from source only, so I want to restrict such case with value zero or blank rather than this message "[-11059 No Good data for Calculation]".
How can I handle such situation?
My expression is :=PIAdvCalcVal(PCB!$G$1,PCB!$A$3,PCB!$B$3,"average","time-weighted",0,1,0,"jplpi")
Why don't you create another column next to this calculation column. Then use an IF statement in Excel to detect the result and output 0 or "" if the result is "[-11059 No Good data for Calculation]" and display the normal value otherwise. Then you can just hide the calculation column after that and only display this new column. Will that work for you?
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
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]
- 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.
- 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",
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