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

```kusto
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:

<figure><img src="/files/onYi2jDKC6gKZNmwTswf" alt=""><figcaption></figcaption></figure>

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:

<figure><img src="/files/hQMxrLffyhtgfJE64HW4" alt=""><figcaption><p>The trend of a timeseries can be calculated using the series_fit_line operator</p></figcaption></figure>

***

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

```kusto
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.

<figure><img src="/files/B1ZcCmPJ0Ge1jDtvzs61" alt=""><figcaption><p>The series_decompose_forecast operator can be used to forecast future values of a timeseries</p></figcaption></figure>

***


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tricloudnexus.io/management-portal/insights/queries/creating-a-query/advanced-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.