Advanced Queries

---

Advanced Queries

In this section, we'll cover some advanced operators used in KQL queries, to help you begin exploring data in Tricloud Nexus.

---

series_fit_line operator (Trend line)

The Query below demonstrates the usage of the series_fit_line operator. The series_fit_line operator performs a linear regression analysis on the time series data. It determines the direction and strength of a trend in the AvgValue series over time. The key output, the TrendLine, provides a smoothed linear approximation of the data, which helps identify patterns or predict future behavior.

let startTime = datetime("2024-10-01 00:00:00");
let endTime = datetime("2025-01-01 00:00:00");
let resolution = 1d;
let tagName = "DA.IM.P103.Line.OrderCompletion";
Measurements
| where StartTimestamp > startTime and StartTimestamp < endTime
| where HierarchicalName has tagName
| extend properties = todynamic(ValueString)
| extend DeliveredItems = toint(properties.deliverQuantiy)
| make-series AvgValue = avg(DeliveredItems) default=0 on StartTimestamp from startTime to endTime step totimespan(resolution)
| extend (RSquare,Slope,Variance,RVariance,Interception,TrendLine)=series_fit_line(AvgValue)
| render timechart

Looking at the raw result of the query, it shows the AvgValue and TrendLine as an Array of values, that can be plotted to a timeChart and will show the AvgValue and Trend of the delivered items. However using the series_fit_line also returns other metrics like RSquare, Slope, Variance, RVariance as seen below:

Here's an explanation of the key metrics from the query result:

RSquare (R²):

• What it measures: The proportion of the variance in the observed data (AvgValue) that is explained by the trend line (TrendLine).

• Value in the result: 0.7435 (approximately 74%).

• Interpretation: About 74% of the variability in the daily average delivered items can be explained by the trend line. This indicates a relatively good fit but leaves room for unexplained variation due to other factors or noise.

Slope:

• What it measures: The rate of change of the trend line over time.

• Value in the result: 0.6919.

• Interpretation: The daily average number of delivered items is increasing at a rate of approximately 0.69 units per day. This indicates a consistent upward trend in deliveries over the analyzed time period.

Variance:

• What it measures: The variability or spread of the observed data (AvgValue).

• Value in the result: 459.1032.

• Interpretation: The observed data has a moderate level of variability, showing fluctuations in the daily delivery averages over time. A high variance typically indicates significant day-to-day changes in deliveries.

RVariance (Residual Variance):

• What it measures: The variance of the residuals, which are the differences between the observed values (AvgValue) and the predicted values on the trend line (TrendLine).

• Value in the result: 117.7715.

• Interpretation: This is the portion of variance in the observed data that is not explained by the trend line. The residual variance is significantly smaller than the total variance, which supports the relatively good R² value and indicates that the trend line captures most of the data's pattern.

Interception:

• What it measures: The y-intercept of the trend line, representing the predicted value of AvgValue when the x-axis (time) starts (e.g., the first day of the time period).

• Value in the result: -12.6436.

• Interpretation: The trend line predicts an initial value of approximately -12.64 for the first day, which is not physically meaningful in this context (as deliveries cannot be negative). This suggests that the trend line is more relevant for capturing the overall slope and pattern rather than precise starting values.

Here is the result of running the Query and rendering it using the timeChart:

The trend of a timeseries can be calculated using the series_fit_line operator

---

Series_decompose_forecast operator

In the following example the series_decompose_forecast operator is used to forecast the average value of the delivered items of workorders 1 week into January 2025, based on average values from December month of 2024. In the make-series operator, we are extending the end time by 7 days to include space for the forecasted points.

let startTime = datetime("2024-12-01 00:00:00");
let endTime = datetime("2024-12-31 00:00:00");
let resolution = 1d;
let tagName = "DA.IM.P103.Line.OrderCompletion";
let forecast_points=7;
Measurements
| where StartTimestamp > startTime and StartTimestamp < endTime
| where HierarchicalName has tagName
| extend properties = todynamic(ValueString)
| extend DeliveredItems = toint(properties.deliverQuantiy)
| make-series AvgValue = avg(DeliveredItems) default=0 on StartTimestamp from startTime to (endTime + forecast_points*resolution) step totimespan(resolution)
| extend Forecast = series_decompose_forecast(AvgValue, forecast_points)
| render timechart

Here is the result of running the Query. The Average DeliveredItems is shown per. day, and the Average DeliveredItems is forecasted 7 days into January.

The series_decompose_forecast operator can be used to forecast future values of a timeseries

---