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

[Sam.G]

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.

[Dan FR]

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 May 6 by fosterda

[Sam.G]

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 May 6 by Sam.G

[Dan FR]

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 May 7 by fosterda
cleaned up code

[Sam.G]

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!

[Sam.G]

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? 

[Chris Harp]

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 11 hours ago by Chris Harp

[Sam.G]

$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.

[Chris Harp]

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())

 

[Sam.G]

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.