Average of data between 2 specified dates

I want and LED display to show the average data between a tag and 2 custom property tags that change when I update the date on a calendar. nothing i find shows me how to do this. Trying to keep it simple and not use tables. I have tried to use the expression property based on the tag and the 2 custom property date/times. I tried system.tag.queryTagCalculations and system.tag.queryTagHistory and none of them seem to work. what am I missing?

Not sure what you mean by this. Are these custom properties on the tags? Or custom properties in your Vision UI somewhere?

You probably want a tag history binding, not a script for this.

Screenshot of your display and your attempt at a binding would help. (Obscure sensitive info.)

i have a tag that takes readings every second. i want to know the average of the data between 2 set time ranges(start and end of shift).

this is what i tried to script.
LineTag = [default]Hi Speed Nordson/Line Speed FPM 1
date1 = event.source.parent.startDate5AM
date2 = event.source.parent.stopDate2PM

event.source.text = system.tag.queryTagCalculations(LineTag,calculations = "Average",date1,date2)

both of these error out and say there is something wrong.

I know there is a syntax thing here that I am not familiar with so I'm a bit lost. I can VBA but python is still Greek.

Please do not post duplicate questions on the forum.
Noob not getting the syntax for average of tag data - General Discussion - Inductive Automation Forum

It looks like you're combining scripting and expression syntax. The expression and scripting languages are completely different. The function you are using is for scripting, but you are trying to use it in an expression which doesn't work.

I typically hold shift datetimes in tags whether they are memory tags or expression tags. You can bind the shift start/end tags to custom properties in a vision project if needed. Write your script and test it in the script console under Tools → Script Console first. (format scripts using the </> button in the forum).

# tag path for line is a string
LineTag = '[default]Hi Speed Nordson/Line Speed FPM 1'
# If you have shift start/end tags, then bind the values to your custom properties
date1= event.source.parent.startDate5AM
date2= event.source.parent.stopDate2PM

event.source.text = system.tag.queryTagCalculations(paths=[LineTag],calculations = ["Average"],startDate=date1,endDate=date2)

Visit www.inductiveuniversity.com and go through the lessons if you haven't already.

EDIT: You have to import the vision window to use those custom properties in the script console, so probably easier to test in button or something. I started down one path of explanation and changed my mind midway. :upside_down_face:

1 Like

The fundamental thing that I think you're missing:
Anywhere in Ignition that says "expressions" means you're using Ignition's expression language. It's not Python, or even a real programming language. It's actually close, conceptually, to Excel formulas - regardless of inputs, an expression must always return a single value.

Scripting in Ignition is comparable to VBA in Excel - it must be specifically invoked from somewhere, and is not how you accomplish most basic tasks (e.g. imagine needing to use VBA to sum up a column in Excel?)

As for your specific question here, you've unfortunately hit a rough edge case where there is no "simple" solution - you have to invoke scripting or use a tag history binding to get this information as a dataset, then extract the specific values you want from that dataset.

1 Like

Apologies on the duplicate.

the date and times are tied to a calendar so when they want to look in the past, they click a dat on the calendar and it repopulates a data table. I don't want to have a ton of random tables if there is a simple script/expression that can do it for me. i.e. something a simple as the system.tag.queryTagCalculation is inviting, but the inputs are the tag with a specific syntax, the custom property date/times tied to the Root Container, and a calculation call out of some sort... at least thats how it looks on paper.

Can i not use the system.tag.queryTagCalculation in the expression? is there something similar?

1 Like

You don't need a visible table to hold any arbitrary variable. You can add custom properties to basically every Vision component, and a custom property can have many different types, including dataset types.

I would approach this problem like this:

  1. Somewhere on your window (could be on the calendar component(s), or the root container, or your data table, whatever) add a custom property of type dataset: Component Properties - Understanding Vision Components
  2. On this custom property, add a Tag History Binding: Tag Historian Binding - Tag Bindings in Vision. You can configure this binding to pass in your start and end date values from the components on the window, and configure it to use the 'Basic Average' aggregation with a fixed return size.
  3. Add an expression binding on the LED display's text property that extracts the relevant cell from the dataset custom property: Expression Binding - Property Bindings in Vision, Expression Language and Syntax - Ignition User Manual 8.1 - Ignition Documentation

I guess that's my point. I know that I can add a table, toss the data on it and go from there. My question revolves around the direct querying of the tag that is already collecting data at a set frequency. The ultimate goal is to not create another table just to get my data out of the database. Yes the tables are hidden, but the background of the Designer gets super messy. Is there no direct way to get the information that is already stored without suing another table? Seems like the system.tag.queryTagCalc or History is the perfect solution. If these work, then how is that incorporated into an expression to output onto the LED display? If not, then I will suffer but quietly. :slight_smile:

Paul's point is you do not need a table to hold a dataset. You can create a custom property on the root container, or another component, and bind the query dataset to that property. Therefore, no extra table is needed.


I think you misunderstood Paul. You don't need to make and then hide a Table component. Just make a custom property that holds a Dataset. It can be a custom property on the LED display component itself, if you wish.

1 Like

light bulb... thanks... apples and oranges do add up :wink: here is what i did incase someone else is looking for it. Its basically what they said up above but in real script...

1)create a CustomProperty that is a dataset.
2)I had a calendar created so i used that as my "event change". when i change the date, i want the data set to change.
3)right click and script in... CustomProperty = system.tag.quertTagHistory(paths=["tag"],startDate=start time. endDate=end time,aggregationMode="SimpleAverage") it's what i needed.
this make the CustomProperty the dataset you need for the next step.
4)Go to your display and use an expression like so... if(now()<OtherCustProperty,"NO DATA",round(mean(CustomProperty,1),2).. spits out the average of the dataset

works like a champ.