var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Difference over time
Apologies in advance if something is wrong as I am quite the novice.
I would like to calculate the difference in a variable over the previous year.
example if I have a table with the price of cheese at the start of each year how would i run a loop that would tell me that change in price from one year to the next.
my data would look like this
Name Price Date
Cheese 1.00 01/01/2000
Cheese 1.10 01/01/2001
Cheese 0.90 01/01/2002
Cheese 1.00 01/01/2003
I would like the output to tbe
Name Price Change Date
Cheese 1.00 0 01/01/2000
Cheese 1.10 0.10 01/01/2001
Cheese 0.90 -0.20 01/01/2002
Cheese 1.00 0.1 01/01/2003
I appreciate all the help and effort
Note I quoted "date" because that is a reserved word.
price - lag(price) over (partition by name order by "date" desc) as change
Thank you so much
Just out of interest how would I increase the lag?
i.e if I had to look back two or three periods instead of just one?
That's totally different from how I'd do it.
Just change the date for o in order to adjust the time period being displayed.
o.price - c.price as change,
cheese as o
inner join cheese as c
o."date" = date '01/01/2000'
and c."date" >= o."date
order by c."date"
Tags for this Thread