I am new to Datalink and I'm not sure how to navigate it yet. I'm looking to make an average of the 2nd, 3rd, 4th and 5th smallest values within an hour for every hour. Any help would be welcome. Many thanks in advance
Thanks for posting on PI Square and Welcome to its community
I'm going to preface this post by saying that there is no Datalink Function/Configuration that will find the nth smallest value. There are some possible workarounds, however.
Method 1: Create a filter expression and use the Calculated Data Function
This method is the easiest to implement. You are essentially calculating the Minimum for a Tag over an interval while applying a filter that will remove the previous minimum values. Here's how to configure this:
Min1 will be the absolute minimum of your time interval. The formula configuration will look something like:
Min2 will be the second lowest value. In order to get this number, we need to filter out Min1. Thus we need add create a filter expression. The syntax of this Excel Formula (Cell F3 in my example) will be:
Where H2 is the value of Min1.
The logic continues with Min3, Min4, and Min5.
Here's where the approach will depend on what you are after. If the tag that you are performing this analysis on is a discrete tag (aka you want an event-weighted average), you can just use the Excel Average Formula to get the average of the 5 values.
This will be a little trickier if you are using a continuous tag (aka you want a time-weighted average). You'll first need to get the start and end times of the time interval. This can be done by using the Min and Max Excel Functions over the 5 returned timestamps (Cells G2 to G6). Finally, you need to create a filter to remove values that are greater than the 5th smallest value (in case the events are not consecutive). The syntax to create the filter will look like:
Once all our pieces are in place, you can then use the Calculated Data Function to calculate the time-weighted average.
Caveats on This Approach: The biggest issue with this approach is that the data must be unique. If there are repeated events then the solution fails. Here's an example set of data that we would like to find the 5th minimum value for: 0, 1, 2, 3, 4, 5, 0, 6. The method will not double count the 0 and assume the 5th smallest value to be 4 and not 3.
Method 2: Brute force and using Microsoft Excel Features to determine the 5 smallest values.
The first step in this method is to use the Compressed Data Function to return all the raw values and timestamps for a given time range.
Then you need to copy and paste the results as values. This way, you can use Excel's sorting features. Sort the results in ascending order. This will give the you timestamp and value of the minimum value and the 5 smallest value. Then we can use the logic described above to find the time-weighted or event-weighted average. Here is an example of a completed solution:
This method will bypass the caveat from Method 1 and will successfully capture duplicate events that are minimums.
Caveat: This is a very manual process. If you needed it to be automatic, you'll need to create a custom function through VBA to do the described steps of repasting the values and sorting.
Summary: All in all, both methods are quite involved and have pros and cons to each. Please let me know if you have any questions or need further clarification.
Many thanks! I’ll give it a try, see if it is possible. It’s mainly to produce reports, and unlucky for me I do have continuous data. I had been using the SMALL function from excel to obtain this from the values I got from PI, but as you said, it’s manual and not streamlined at all.
Retrieving data ...