I have a user who wants to see only data for last 3 months. if the tag doesn't have a value in last 3 months it should be empty. If it has more than one value in 3 months it should show the latest value.
1. Use DataLink Archive Value with "*" as timestamp and retrieval mode "Previous only" - you will get timestamp and value of the last archived event.
2. Use Excel subtraction to calculate the difference between current time (Excel function NOW) and returned timestamp.
3. Use Excel IF function to fill result cell with the value returned in (1.) or an empty string, based on difference calculated in (2.)
If it works as expected, you can pack everything in one formula, if you wish.
Yep. I would use the compressed data function to do this. If you use the task pane to enter your time range and data item, the compressed data function will give you all the results in that time range. But if you take the formula generated by that compressed data function and enter into a single cell, it will give you only the first datapoint received. If there is no data, it will return "No more values:". If it must be blank you can nest the compressed data formula in a substitute function to handle it. The final formula will look like this:
=SUBSTITUTE(PICompDat("TagNameHere","*","*-3mo",8,"","inside"),"No more values:","")
If you'd like it to return the timestamp as well, place the below formula in two cells side by side (A1:A2) using ctrl-shift-enter to enter the formula as an array:
=SUBSTITUTE(PICompDat("TagNameHere","*","*-3mo",9,"","inside"),"No more values:","")
so when i enter the formula to a single cell it returns "resize to show all values".
You will have to check 'Disable "Resize to show all values" message' in the PI Datalink Settings.
That doesn't work because there are other tags in the report and i do not know how it will impact.
what i was thinking is get the compressed data for this particular tag at different location on the excel sheet and reference the first value cell to the appropriate location on the report.
Retrieving data ...