Is there a way to find the second highest value over the course of a year using the PIAdvCalcVal function on DataLink?
So I wasn't as tied up as Dan when I saw this and managed to work up an example that uses both Dan's method and also accomplishes the same thing in a single Calculated Data function. First a screenshot of the sheet:
Everything in a red box uses Calculated Data to find a Max (event weighted) and everything in blue uses an Excel function.
First Dan's suggestion:
D3:E3 is the max of the tag over the entire time range from start time to end time.
F3 subtracts 1s from the timestamp of the max value
G3:H3 is the max from the start time to 1s before the max time.
I3 adds 1s to the timestamp of the max value
J3:K3 is the max from 1s after the max time to the end time
L3 uses Excel's max function to find the max of H3 and K3.
For the second solution (row 6), K6:L6 finds the max over the time range where the value of the tag is less than the max value of the tag. This is accomplished by using the filter function 'tag' < TagMax('tag','<start time>','<end time>') which is built dynamically in A6 using Excel's Concatenate function. As you can see, the values in L3 and L6 are the same.
I'll leave it up to you which approach you want to take, but please keep us posted on your progress. An example sheet is attached. Best of luck!
Tied up at the moment so I can't give a complete solution. I would first finding the timestamp of the max. From there you could search on either side of that timestamp for the max and return the higher of the two sides.
Thanks for posting. I like the use of the filter!
Related the advices of Brent. There can be configured as a PI Tag or Analyses Atribute in AF with the next expression in case this data need to be historized.
Retrieving data ...