12 Replies Latest reply: Jun 22, 2017 1:03 PM by David Forest

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

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
• ###### Re: Hi All, I'm new to Qlik and could use some help.

[price temp]:

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

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];

!

• ###### Re: Hi All, I'm new to Qlik and could use some help.

sample app

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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.

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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 .

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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.

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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.

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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()

• ###### Re: Hi All, I'm new to Qlik and could use some help.

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

• ###### Re: Hi All, I'm new to Qlik and could use some help.

I just noticed that the formula only works when price changes in same month. If month changes in the next row with a higher price for the same part, it won't calculate.  ??

• ###### Re: Hi All, I'm new to Qlik and could use some help.

yes, above and Peek, not sure which method you're referring to, by default go back one row in the data. if you have more than one row per month, you'd have to summarize the data at the month level, avg or max or FirstSortedValue using -Date to get last.