Calculate % difference

[dmxmikey]

I need some help in a scrip that will calculate what the difference % is from prior date (ie cost increase or decrease for the product as in below table. i need to calculate the How to field.

Product	Description	Price date	Cost	How to
00028U	Tube	14/04/2010	£2.70
00028U	Tube	02/08/2010	£2.97	10.00%
00028U	Tube	06/01/2014	£2.97	0.00%
00028U	Tube	30/04/2020	£3.12	4.98%
00028U	Tube	29/05/2020	£3.12	0.00%
00028U	Tube	19/04/2021	£3.59	15.01%
00028U	Tube	10/06/2021	£3.59	0.00%
00028U	Tube	01/08/2021	£4.12	15.00%

[pedrobergo · Accepted solution]

Hi @dmxmikey ,

You need to use the Peek() function. It returns the last row loaded, so you can use COST - line current and PEEK(COST) - previous line.

TableDif:
NoConcatenate
Load
Product,
Description,
[Price date],
Cost,
[How to],
if(Peek(Product)=Product,
(Cost / Peek(Cost)-1) * 100,
0) AS [How to New]
Resident Table
Order By Product, [Price date];

[],

Pedro

[Kushal_Chawda]

@dmxmikey  try below

Data:
LOAD Product, 
     Description, 
     [Price date], 
     Cost
FROM Table;

Final:
LOAD *,
     if(Product=Previous(Product),num( Cost / Peek(Cost)-1,'#0.00%')) as %Diff
Resident Data
Order by Product,Cost,[Price date];

DROP Table Data;