# Finding conditional monthly averages of measurements by product code one year in the past

Go to solution Solved by Dan FR,

## Recommended Posts

The goal: to build a table comparing the averages of several measurements from each production run to the monthly averages for the same product code from the same month a year prior -- but only from runs that meet "good run" criteria. So something like the attached image.

What I'm having trouble with is the previous year monthly averages. I need to not only take monthly averages, but only averages from periods that meet a specific condition: when all three measurements are above setpoint. The idea here is we want to establish what "good" averages looked like during similar atmospheric conditions, and compare recent run averages to them. Additionally, we need to separate these out by Product ID. There are many different products, so manually specifying them all is not feasible.

Example case:

It's May 2024 and we just finished running Product A. We want to see how the measurement averages compare with last year, same month runs. We ran Product A ten times in May 2023, but only certain parts of those ten runs met the "good run" condition. We want to take the average of all of those "good run" periods for Product A from May 2023.

Happy to provide any other information that might be helpful.

##### Share on other sites

• Seeq Team
Posted (edited)

Hi Sam,

Thanks for your questions.  I have a partial answer for you, but I'll need to follow up regarding Product ID grouping.

To find the previous year's monthly averages under good run conditions, you can try the following within Formula Tool.  Assuming '\$good_runs' is what you have defined as your composite good run condition and '\$monthly' is a monthly periodic condition:

// keep signal \$a only when \$good_runs is true
\$a_cleansed = \$a.within(\$good_runs)

// shift our cleansed signal to represent data from one year prior
\$last_year_cleansed = \$a_cleansed.move(1yr)

// Aggregate the shifted signal to calculate monthly averages. This step can optionally be completed using the Signal From Condition Tool
\$last_year_monthly_avg = \$last_year_cleansed.aggregate(average(), \$monthly, durationKey())

// return final computed monthly average signal
\$last_year_monthly_avg

Let me know if that helps.

Unfortunately, grouping by Product ID in Workbench isn't straightforward and will likely require a datalab script.  Could you share the format of the Product ID data in Seeq - ie. is it a string signal, capsule property, etc.?

Edited by fosterda
##### Share on other sites

Posted (edited)

Appreciate the reply, fosterda. That does help, though it's hard to confirm without the Product ID filtering piece haha.

The original Product Type signal is a string, and I have a formula that converts that signal to a condition (\$product_type.toCondition('Product ID')). The formula also applies three properties to the condition, each being the average of one of the measurements we're focused on.

Edited by Sam.G
##### Share on other sites

• Seeq Team
• Solution
Posted (edited)

Hi Sam,

Thanks for your response and the additional information.  To create the table you need, we can utilize the transform function along with a capsule expression to iterate through each product run capsule. Within this loop we will calculate the previous year averages and dynamically set these to capsule properties based on the Product ID.  If you're not familiar with transforms and capsule expressions, here is a good Tips and Tricks article

Below is an example of formula code you can integrate with your existing Product Type signal to condition formula.

// existing \$product_type.toCondition('Product ID') code here ...
// I am assuming this is defined as \$product_condition

// if necessary, define your measurement signals filtered for \$good_runs
\$a_good = \$a.within(\$good_runs)
\$b_good = \$b.within(\$good_runs)
\$b_good = \$b.within(\$good_runs)

\$months = months() // monthly periodic condition

// Loop through each capsule in \$product_condition and calculate last year's measurement averages
\$product_condition.transform(\$capsule -> {
\$productid = \$capsule.property('Product ID') // get Product ID from the current capsule

// Extract a monthly capsule from \$months one year prior to the current capsule. Note that your current capsule could span two months.
// .first() selects the month based on the start month of the capsule.  Change this to .last() if you want the ending month.
\$previous_year_month_capsule = \$months.toGroup(capsule(\$capsule.startKey()-1y,\$capsule.endKey()-1y)).first()

// Calculate scalar averages over the previous year month capsule, filtered for current product ID
\$a_avg = \$a_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)
\$b_avg = \$b_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)
\$c_avg = \$c_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)

// Set averages as capsule properties
\$capsule.setProperty('Previous Year Month Average A', \$a_avg)
.setProperty('Previous Year Month Average B', \$b_avg)
.setProperty('Previous Year Month Average C', \$c_avg)
})

From here, create a Condition Table and use the Columns selector to pick each of the Capsule properties you want in the table.

Here's an example of how you might set up the Condition Table using the Example >> Batch Data >> Reactor 1 data source:

We'll mostly ignore my previous reply 😅 - helpful if you want the previous year average signals for analysis but not for the table filtered by Product ID.

Hope this helps!

Dan

Edited by fosterda
cleaned up code
• 1
• 1
##### Share on other sites

This was incredibly helpful, thank you! I'll need to tweak this slightly as I've realized I'll need to filter by Product ID and an additional variable, but think what you've shared here is enough for me to figure out the rest. Will reply back if not. Thanks again!

• 1
##### Share on other sites

• 2 weeks later...

I think I have this working now, but there is one big difference -- every time I tried to include the toCondition section for Product ID, I'd get an error saying it was expecting EOF. So I left that section in a separate condition, but I think that's giving me some issues when it comes to the table. How can I put all the code in one formula? Do I need to add some symbol to "end" the toCondition part and allow the script to proceed?

##### Share on other sites

• Seeq Team
Posted (edited)
2 hours ago, Sam.G said:

I think I have this working now, but there is one big difference -- every time I tried to include the toCondition section for Product ID, I'd get an error saying it was expecting EOF. So I left that section in a separate condition, but I think that's giving me some issues when it comes to the table. How can I put all the code in one formula? Do I need to add some symbol to "end" the toCondition part and allow the script to proceed?

Can you provide your formula with the toCondition?

Edited by Chris Harp
##### Share on other sites

\$pt.toCondition('Product ID')

.removeLongerThan(100d)
.setProperty('Avg A', \$a, average())
.setProperty('Avg B', \$b, average())
.setProperty('Avg C', \$c, average())

Sorry, should have provided that before. If I add anything below this code it gives me the EOF error.

##### Share on other sites

• Seeq Team

I did not see any issues with your toCondition.

I was able to successfully execute this Formula as a test.  \$good_runs needs to be redefined to match your requirements.

\$product_condition = \$pt.toCondition('Product ID').setMaximumDuration(100d)
\$good_runs = condition(capsule('2023'))

\$a_good = \$a.within(\$good_runs)
\$b_good = \$b.within(\$good_runs)
\$c_good = \$c.within(\$good_runs)

\$months = months() // monthly periodic condition

// Loop through each capsule in \$product_condition and calculate last year's measurement averages
\$product_condition.transform(\$capsule -> {
\$productid = \$capsule.property('Product ID') // get Product ID from the current capsule

// Extract a monthly capsule from \$months one year prior to the current capsule. Note that your current capsule could span two months.
// .first() selects the month based on the start month of the capsule.  Change this to .last() if you want the ending month.
\$previous_year_month_capsule = \$months.toGroup(capsule(\$capsule.startKey()-1y,\$capsule.endKey()-1y)).first()

// Calculate scalar averages over the previous year month capsule, filtered for current product ID
\$a_avg = \$a_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)
\$b_avg = \$b_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)
\$c_avg = \$c_good.within(\$product_condition.keep('Product ID', isEqualTo(\$productid))).average(\$previous_year_month_capsule)

// Set averages as capsule properties
\$capsule.setProperty('Previous Year Month Average A', \$a_avg)
.setProperty('Previous Year Month Average B', \$b_avg)
.setProperty('Previous Year Month Average C', \$c_avg)
})
.setProperty('Avg A', \$a, average())
.setProperty('Avg B', \$b, average())
.setProperty('Avg C', \$c, average())

##### Share on other sites

Hmm.. so the rest of the code block needs to go before the .setProperty section?

I'm still getting the same error, see attached. The "good runs" condition already exists, which is why I'm not defining it within the code, but can add that if necessary.

##### Share on other sites

• Seeq Team

If an expression isn't assigned to a variable in formula, Seeq expects it to be the formula output, and that output is expected to be the last expression in the formula. That's where your error is coming from -- your line 1 isn't assigned to a variable so Seeq expects it to be the formula output.

• 1
##### Share on other sites

21 hours ago, John Brezovec said:

If an expression isn't assigned to a variable in formula, Seeq expects it to be the formula output, and that output is expected to be the last expression in the formula. That's where your error is coming from -- your line 1 isn't assigned to a variable so Seeq expects it to be the formula output.

Thank you, this resolved the issue.