&Variance Vs previous record

[E9-Tech]

How do I go about calculating the % variation based on the previous record

E.g. below I would like on 2nd record to show 20% as the increase versus the previous value of 100, on the last record should be 8.33 increase from 120

 

[MajP]

ID	DateFrom	TotValue	PreviousValue	Difference	PercentChange
1	12/8/2024	100
2	2/3/2025	120	100	20	20.00%
3	3/1/2025	130	120	10	8.33%

Query1

SELECT table1.id,
       table1.datefrom,
       table1.totvalue,
        (SELECT TOP 1 A.totvalue
        FROM   table1 AS A
        WHERE  A.id < table1.id
        ORDER  BY A.id DESC)          AS PreviousValue,
       [totvalue] - [previousvalue]   AS Difference,
       [difference] / [previousvalue] AS PercentChange
FROM   table1;

 

[cheekybuddha]

See here on Allen Browne's site for a method to get a value in the previous row.

First, get rid of the Variance column in your table - this is a calculated value and shouldn't be stored.

Then use SQL like:

SELECT
  t.ID,
  t.DateFrom,
  t.TotValue,
  (
    SELECT TOP 1
      t1.TotValue
    FROM Table1 t1
    WHERE t1.DateFrom < t.DateFrom
    ORDER BY
      t1.DateFrom DESC
  ) AS TotPrev,
  Round(((t.TotValue - TotPrev) / TotPrev) * 100, 2) AS Variance
FROM Table1 t
;

 

[MajP]

@E9-Tech
In a query you can use the format properties on a column to set the formatting. There is really no need to round a shown above. You can pick percent and choose number of decimals or leave as a number and set number of decimal places.