Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dmxmikey
Creator
Creator

Calculate % difference

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.

 

ProductDescriptionPrice dateCostHow to
00028UTube14/04/2010£2.70 
00028UTube02/08/2010£2.9710.00%
00028UTube06/01/2014£2.970.00%
00028UTube30/04/2020£3.124.98%
00028UTube29/05/2020£3.120.00%
00028UTube19/04/2021£3.5915.01%
00028UTube10/06/2021£3.590.00%
00028UTube01/08/2021£4.1215.00%

 

1 Solution

Accepted Solutions
pedrobergo
Employee
Employee

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

 

View solution in original post

2 Replies
pedrobergo
Employee
Employee

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;