Hi All, I'm new to Qlik and could use some help.

[#1]

I have pivot table that looks like the one below. I'm trying to take the difference between price and prior price (for same Lot# and plant), if increase in price, then multiply it by quantity sold at price increase. If no increase then 0.  Thank you.

Rows					Measures
Lot#	Customer	Plant	Month	Price	Quantity Sold	Impact
0021	Johnnies	5	8	$ 4.35	2
0021	Johnnies	5	9	$ 5.01	25	=if(5.01>4.35 for same Lot# and plant, then Impact =(5.01-4.35) *25
0021	Johnnies	5	10	$ 5.10	21
0021	Johnnies	5	11	$ 5.10	23
0021	Johnnies	5	12	$ 5.25	10
0035	Joney	7	8	$ 2.67	6
0035	Joney	7	9	$ 2.80	4
0035	Joney	7	10	$ 2.85	53
0035	Joney	7	11	$ 2.90	5

[dwforest]

[price temp]:

LOAD *

INLINE

[

"Lot#", "Customer", "Plant", "Month", "Price", "Quantity Sold"

'0021', 'Johnnies', 5, 8, 4.35, 2                                                                  

'0021', 'Johnnies', 5, 9, 5.01, 25

'0021', 'Johnnies', 5, 10, 5.10, 21

'0021', 'Johnnies', 5, 11, 5.10, 23

'0021', 'Johnnies', 5, 12, 5.25, 10

'0035', 'Joney', 7, 8, 2.67, 6

'0035', 'Joney', 7, 9, 2.80, 4

'0035', 'Joney', 7, 10, 2.85, 53

'0035', 'Joney', 7, 11, 2.90, 5

];

[price]:

NoConcatenate

LOAD *,

if(peek("Lot#")="Lot#" and peek(Plant)=Plant and Peek(Price)<Price, Price-Peek(Price)*"Quantity Sold",0) AS Impact

RESIDENT [price temp]

ORDER BY "Lot#",Month;

DROP TABLE [price temp];

!

[dwforest]

sample app

[#4]

Thank you for the quick response David.  I will try this once IT gets me the proper rights.

[mjtaft2017]

There was one error in your solution which I fixed.  It was yielding negative values

So I updated it to fix that ( now shows 16.50 instead of -103.74 for impact on line 2 ) -- and changed up the script to eliminate the temp table.

[Price Updated]:

Load *, if(peek("Lot#")="Lot#" and peek(Plant)=Plant and Peek(Price)<Price, (Price-Peek(Price))*"Quantity Sold",0) AS Impact

INLINE

[

"Lot#", "Customer", "Plant", "Month", "Price", "Quantity Sold"

'0021', 'Johnnies', 5, 8, 4.35, 2                                                                  

'0021', 'Johnnies', 5, 9, 5.01, 25

'0021', 'Johnnies', 5, 10, 5.10, 21

'0021', 'Johnnies', 5, 11, 5.10, 23

'0021', 'Johnnies', 5, 12, 5.25, 10

'0035', 'Joney', 7, 8, 2.67, 6

'0035', 'Joney', 7, 9, 2.80, 4

'0035', 'Joney', 7, 10, 2.85, 53

'0035', 'Joney', 7, 11, 2.90, 5

];

I'm new too so I wanted to try out your solution and experiment with preceding load.  Thank you.

[mjtaft2017]

James - I updated David's solution to correct a small bug.  Being new also - I posted it as a reply to him when perhaps maybe I needed to put as a reply on the original thread.  I did add a change to eliminate the temp table - since I was trying out the preceding load .

[#7]

Thanks Mary Jo,  I have a question about the [ ]; at the end below INLINE with the column headings and the rows of data.  That was a table I made up so you could see the structure of the table I was creating.  My data table before the pivot is approx. 750,000 rows. I was trying to get the script to enter in the App script for the pivot. How do I just enter the script to perform the added calculation and add the Impact column to table?  Like I said I'm very new to this.

[mjtaft2017]

James,

I am new to this too.  It sounds like you have a qvf started - is it possible to upload at least the script for the table you are referring to?  In the meanwhile - let me look for the example I created which doesn't use an inline table - that might answer your question.

[dwforest]

INLINE  is a way to quickly add some data, the Load*, if(peek("Lot#")="Lot#" and peek(Plant)=Plant and Peek(Price)<Price, (Price-Peek(Price))*"Quantity Sold",0) AS Impact could be added above any other SQL SELECT or LOAD statement

this could also be done in the UI as a measure using Above() instead of Peek()

[#10]

Thank you David for letting me know I could use Above() in UI. I did this and it worked perfectly.  I can't thank you and Mary Jo enough for all your help.  I have a cookbook on Qlik coming today and can't wait to dig into it.

Best regards,

Jim