1 (edited by jrga 2025-09-14 20:46:19)

Topic: Trendline on Bar Chart (MVD 5.6)

Eureka! I managed to add a trendline to a bar chart (MVD 5.6) with the help of ChatGPT. I searched the forum for a solution, but couldn't find one.


Var
   ChartBar: TChart;

procedure Form1_OnShow (Sender: string; Action: string);
begin

    // CHARTBAR====================================================================
    ChartBar := TChart.Create(Form1);
    ChartBar.Parent := Form1.PanelBar;
    ChartBar.Align := alClient;
    ChartBar.AddSeries(TBarSeries.Create(ChartBar));

    TBarSeries(ChartBar.Series[0]).Marks.Visible := False;
    ChartBar.Axes.Bottom.LabelsAngle := 90;
    ChartBar.View3DOptions.Zoom := 90; // Ajuste o zoom para caber todos os labels
    ChartBar.Legend.Visible := true;
    Form1.bBarUpdate.Click;
end;

procedure Form1_bBarUpdate_OnClick (Sender: TObject; var Cancel: boolean);
var
    Results: TDataSet;
    sData: string;
    sValue: string;
    x: integer;
    sumX, sumY, sumXY, sumX2: Double;
    n: Integer;
    a, b: Double;  // Coeficientes da reta Y = aX + b
    firstX, lastX: Integer;
begin
    ChartBar.Series[0].Clear;
    if ChartBar.SeriesCount < 2 then
        ChartBar.AddSeries(TLineSeries.Create(ChartBar)); // cria série p/ tendência

    ChartBar.Series[1].Clear;  // limpa a linha de tendência
    TLineSeries(ChartBar.Series[1]).SeriesColor := clRed; // cor da tendência

    x := 1;
    sumX := 0;
    sumY := 0;
    sumXY := 0;
    sumX2 := 0;
    n := 0;

    SQLQuery('SELECT strftime("%d/%m/%Y",Data) as DMA, valor FROM tbl_medicoes WHERE'+
    ' id_tbl_parametros = '+inttostr(form1.cbo_parametro.dbItemID)+' and id_tbl_usuarios = '+inttostr(form1.cbo_user.dbItemID)+
    ' and Data >= '+form1.dt_ano_mes_ini.sqlDate+' and Data <= '+form1.dt_ano_mes_fim.sqlDate+' and'+
    ' case when length("'+form1.cbo_sit.text+'") = 0 then 1=1 else id_tbl_situacao = ' + inttostr(form1.cbo_sit.dbItemID)+' end and'+
    ' case when length("'+form1.cbo_status.text+'") = 0 then 1=1 else id_tbl_status = ' + inttostr(form1.cbo_status.dbItemID)+' end'+
    ' ORDER BY Data ASC', Results);

    while not Results.Eof do
    begin
        sData := Results.FieldByName('DMA').asString;
        sValue := Results.FieldByName('valor').asString;


        if sValue <> '' then
        begin
            ChartBar.Series[0].AddXY(
                x,
                StrToFloat(ReplaceStr(sValue, '.', ',')),
                sData,
                RandomColor()
            );

            // acumula p/ regressão linear
            sumX  := sumX + x;
            sumY  := sumY + StrToFloat(ReplaceStrEx(sValue, '.', ','));
            sumXY := sumXY + (x * StrToFloat(ReplaceStrEx(sValue, '.', ',')));
            sumX2 := sumX2 + (x * x);
            Inc(n);

            if n = 1 then firstX := x;
            lastX := x;
        end;

        Results.Next;
        Inc(x);
    end;

    // calcula coeficientes da reta
    if n > 1 then
    begin
        a := (n*sumXY - sumX*sumY) / (n*sumX2 - sumX*sumX); // inclinação
        b := (sumY - a*sumX) / n;                          // intercepto

        // adiciona 2 pontos extremos p/ desenhar linha de tendência
        ChartBar.Series[1].AddXY(firstX, a*firstX + b, 'Tendência', clRed);
        ChartBar.Series[1].AddXY(lastX, a*lastX + b, '', clRed);
    end;

    ChartBar.Series[0].Title := 'Valores';
    ChartBar.Series[1].Title := 'Tendência';

end;


function ReplaceStrEx(AText, FromText, ToText: string): string;
var //*** Proc 60 - replace por Dmitry
    ArStr: array of string;
    i, c: integer;
begin
    ArStr := SplitString(FromText, ',');
    c := Length(ArStr)-1;
    for i := 0 to c do
    begin
        AText := ReplaceStr(AText, ArStr[i], ToText);
    end;
    result := AText;
end;

function RandomColor: TColor;
begin // Função para gerar uma cor aleatória em barras dos gráficos
    Result := RGB(Random(256), Random(256), Random(256));
end;
Post's attachments

Attachment icon trend_line.png 14.27 kb, 167 downloads since 2025-09-14 

Roberto Alencar

Re: Trendline on Bar Chart (MVD 5.6)

Hi jrga

This proves that what we feared is going to happen.
AI is going to destroy a lot of jobs, first and foremost the entire
world of programmers.
You Give us an example.
JB

Re: Trendline on Bar Chart (MVD 5.6)

Hi Roberto, Jean


Beautiful.
Instead of TBarSeries you can use TPointSeries (points), especially if there is a lot of data on the chart. In 3D they do not look very good, so you can switch to the standard mode ChartBar.View3D := False;

Post's attachments

Attachment icon chart.jpg 53.43 kb, 161 downloads since 2025-09-14 

Re: Trendline on Bar Chart (MVD 5.6)

jean.brezhonek wrote:

Hi jrga

This proves that what we feared is going to happen.
AI is going to destroy a lot of jobs, first and foremost the entire
world of programmers.
You Give us an example.
JB


Hi JB, AI makes a lot of mistakes! In the case of the trendline, I had to explain what I wanted it to do several times. It made mistakes and repeated some errors. I had to correct parts of the generated script, including the final script. I provided a starting script containing a function created by Dmitry himself (ReplaceStrEx). The AI insisted on giving this function a different name. It takes time to get a result, which often occurs through trial and error. It can help in some cases and speed up problem-solving, but only occasionally. I doubt it will ever fully replace the work of a programmer.

Roberto Alencar

Re: Trendline on Bar Chart (MVD 5.6)

p.s.

When receiving a result from the database, you can immediately use

sValue := Results.FieldByName('valor').asFloat;
or
sValue := Results.FieldByName('valor').asCurrency;

depending on the type of data in the valor column.
and so on.
After this, you will not need to replace in the line and convert to a floating format. Check for ValidFloat();

6 (edited by jrga 2025-09-14 22:28:16)

Re: Trendline on Bar Chart (MVD 5.6)

Now the trend line is for line chart.

Var
     ChartLine : TChart;

procedure Form1_bLinUpdate_OnClick (Sender: TObject; var Cancel: boolean);
//*** Proc 1334 - LINHA DE TENDÊNCIA GRÁFICO DE LINHA - CHATGPT
var
  Results   : TDataSet;
  DateValue : Double;    // valor TDateTime vindo do SQL
  sValor    : String;
  yVal      : Double;

  // somatórios para regressão (usamos X deslocado = Data - BaseDate)
  sumX, sumY, sumXY, sumX2: Double;
  n: Integer;
  a, b: Double;          // coeficientes da reta y = a*Xoffset + b
  BaseDate, FirstDate, LastDate: Double;
  Xoffset: Double;
  Trend: TLineSeries;
begin
  // Garante que exista ao menos uma série para os valores
  if ChartLine.SeriesCount = 0 then
    ChartLine.AddSeries(TLineSeries.Create(ChartLine));

  // Limpa série de valores
  ChartLine.Series[0].Clear;

  // Assegura que exista a série 1 para a tendência e que seja TLineSeries
  if ChartLine.SeriesCount < 2 then
    ChartLine.AddSeries(TLineSeries.Create(ChartLine))
  else if not (ChartLine.Series[1] is TLineSeries) then
  begin
    ChartLine.Series[1].Free;
    ChartLine.AddSeries(TLineSeries.Create(ChartLine));
  end;

  Trend := TLineSeries(ChartLine.Series[1]);
  Trend.Clear;
  Trend.SeriesColor := clRed;
  Trend.Title := 'Tendência';

  // Formatação do eixo X como data (ajuste visual)
  ChartLine.Axes.Bottom.LabelStyle := talValue;
  ChartLine.Axes.Bottom.DateTimeFormat := 'dd/mm/yyyy';
  ChartLine.Axes.Bottom.LabelsAngle := 90;
  ChartLine.Legend.Visible := True;

  // Inicializa acumuladores
  sumX := 0; sumY := 0; sumXY := 0; sumX2 := 0;
  n := 0;
  BaseDate := 0; FirstDate := 0; LastDate := 0;

  // Query (ordem ASC para cronologia)
  SQLQuery(
    'SELECT Data, valor FROM tbl_medicoes WHERE'+
    ' id_tbl_parametros = '+IntToStr(Form1.cbo_parametro.dbItemID)+' and id_tbl_usuarios = '+IntToStr(Form1.cbo_user.dbItemID)+
    ' and Data >= '+Form1.dt_ano_mes_ini.sqlDate+' and Data <= '+Form1.dt_ano_mes_fim.sqlDate+' and'+
    ' case when length("'+Form1.cbo_sit.Text+'") = 0 then 1=1 else id_tbl_situacao = ' + IntToStr(Form1.cbo_sit.dbItemID)+' end and'+
    ' case when length("'+Form1.cbo_status.Text+'") = 0 then 1=1 else id_tbl_status = ' + IntToStr(Form1.cbo_status.dbItemID)+' end'+
    ' ORDER BY Data ASC',
    Results
  );

  // Percorre resultados — plota pontos e acumula para regressão (usando offset de data)
  while not Results.Eof do
  begin
    DateValue := SQLDateTimeToDateTime(Results.FieldByName('Data').AsString);
    sValor := Results.FieldByName('valor').AsString;

    // tenta converter o valor (aceita "12,5" ou "12.5")
    try
      yVal := StrToFloatLocale(sValor);
    except
      // se não for número válido, ignora este registro
      Results.Next;
      Continue;
    end;

    // primeira data define base (offset = 0)
    if n = 0 then
    begin
      BaseDate := DateValue;
      FirstDate := DateValue;
    end;

    LastDate := DateValue;
    Xoffset := DateValue - BaseDate; // em dias (Double)

    // adiciona ponto real no gráfico (eixo X = DateValue)
    ChartLine.Series[0].AddXY(DateValue, yVal);

    // acumula somatórios usando Xoffset
    sumX  := sumX + Xoffset;
    sumY  := sumY + yVal;
    sumXY := sumXY + (Xoffset * yVal);
    sumX2 := sumX2 + (Xoffset * Xoffset);
    Inc(n);

    Results.Next;
  end;

  // calcula regressão (mínimos quadrados) sobre Xoffset
  if n > 1 then
  begin
    // coeficiente angular a e intercepto b (em relação a Xoffset)
    a := (n * sumXY - sumX * sumY) / (n * sumX2 - sumX * sumX);
    b := (sumY - a * sumX) / n;

    // Desenha a linha de tendência usando as datas reais (primeira e ultima)
    // yParaFirst = a*(FirstDate-BaseDate) + b  (FirstDate-BaseDate = 0)
    Trend.AddXY(FirstDate, a * 0 + b, '', clRed);
    Trend.AddXY(LastDate,  a * (LastDate - BaseDate) + b, '', clRed);
  end;

  // Títulos para a legenda
  ChartLine.Series[0].Title := 'Valores';
  Trend.Title := 'Tendência';
end;

// Função auxiliar que tenta converter string float aceitando pontos ou vírgulas
function StrToFloatLocale(const S: string): Double;
var
  s1: string;
begin
  // tenta conversão direta (sistema local)
  try
    Result := StrToFloat(S);
    Exit;
  except end;

  // tenta trocar '.' por ',' (ex.: "2.25" -> "2,25")
  s1 := ReplaceStr(S, '.', ',');
  try
    Result := StrToFloat(s1);
    Exit;
  except end;

  // tenta trocar ',' por '.'
  s1 := ReplaceStr(S, ',', '.');
  Result := StrToFloat(s1);
end;

procedure Form1_OnShow (Sender: string; Action: string);
begin

    // CHARTLINE====================================================================
    ChartLine := TChart.Create(Form1);
    ChartLine.Parent := Form1.Panel1;
    ChartLine.Align  := alClient;
    ChartLine.AddSeries(TLineSeries.Create(ChartLine));
    ChartLine.Series[0].XValues.DateTime := True;
    ChartLine.BottomAxis.LabelsAngle     := 90;
end;

function ReplaceStrEx(AText, FromText, ToText: string): string;
var //*** Proc 60 - replace por Dmitry
    ArStr: array of string;
    i, c: integer;
begin
    ArStr := SplitString(FromText, ',');
    c := Length(ArStr)-1;
    for i := 0 to c do
    begin
        AText := ReplaceStr(AText, ArStr[i], ToText);
    end;
    result := AText;
end;
Post's attachments

Attachment icon trend_line2.png 11.47 kb, 146 downloads since 2025-09-15 

Roberto Alencar

Re: Trendline on Bar Chart (MVD 5.6)

new function

Post's attachments

Attachment icon chrtpnt trend.zip 23.14 kb, 202 downloads since 2025-10-05 

Re: Trendline on Bar Chart (MVD 5.6)

Kudos to you Sparrow - that's some impressive coding smile
Derek.

Re: Trendline on Bar Chart (MVD 5.6)

Hi Derek,
thanks.

Another version of the example. This example differs in that the x-axis is represented as time. Since the sampled time intervals in our example are uneven, this example should, in theory, be the correct representation of time. But as physicists say: Everything depends on the observer's frame of reference. So, the example is a matter of personal preference. The calculations have changed slightly. The x-axis labels can be customized. See the comment in the script.
I'd be happy if the examples are useful in your work.

Post's attachments

Attachment icon chrtpnt trend by date.zip 24.88 kb, 198 downloads since 2025-10-06 

Re: Trendline on Bar Chart (MVD 5.6)

sparrow wrote:

Hi Derek,
thanks.

Another version of the example. This example differs in that the x-axis is represented as time. Since the sampled time intervals in our example are uneven, this example should, in theory, be the correct representation of time. But as physicists say: Everything depends on the observer's frame of reference. So, the example is a matter of personal preference. The calculations have changed slightly. The x-axis labels can be customized. See the comment in the script.
I'd be happy if the examples are useful in your work.

Hi Sparrow, as I mentioned in another thread, I moved and was without internet for a few days. Congratulations on improving the Trend Curve. It's really good! Congratulations to everyone for dedicating some of their time to improving other users' projects, answering questions, and contributing to the growth of the forum and the learning experience of all who love MVD!

Roberto Alencar

Re: Trendline on Bar Chart (MVD 5.6)

Hi Roberto


To celebrate the move and getting internet, we'll release the final graph examples. ))) With new charts, of course.

Post's attachments

Attachment icon Examples.zip 58.2 kb, 214 downloads since 2025-10-08 

Re: Trendline on Bar Chart (MVD 5.6)

sparrow wrote:

Hi Roberto


To celebrate the move and getting internet, we'll release the final graph examples. ))) With new charts, of course.


Hi Sparrow, congratulations! It's really good! If I don't find a solution on the forum, I won't ask ChatGPT, Gemini, or Copilot (all free versions) anymore. I'll ask you... and I'll be without internet more often... just kidding. Hugs

Roberto Alencar

Re: Trendline on Bar Chart (MVD 5.6)

I need to say thank you.
I learned what I didn't learn in school and expanded my knowledge in the field of TChar, charts, trend lines and many other things where it is applied.

Re: Trendline on Bar Chart (MVD 5.6)

+1

Domebil

Re: Trendline on Bar Chart (MVD 5.6)

Hi all.

If you like smoothed graphs, this is for you.
Since the graph is smoothed using parabolas, the control points are located on the graph line, but not always at the peaks.
When smoothing is applied, the control points are marked separately with circles.

Post's attachments

Attachment icon Examples2.zip 68.91 kb, 218 downloads since 2025-10-14