Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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#CustomerPlantMonth Price Quantity SoldImpact
0021Johnnies58 $ 4.35 2                                                                   
0021Johnnies59 $ 5.01 25=if(5.01>4.35 for same Lot# and plant, then Impact =(5.01-4.35) *25
0021Johnnies510 $ 5.10 21
0021Johnnies511 $ 5.10 23
0021Johnnies512 $ 5.25 10
0035Joney78 $ 2.67 6
0035Joney79 $ 2.80 4
0035Joney710 $ 2.85 53
0035Joney711 $ 2.90 5
11 Replies
dwforest
Not applicable

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

[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
Not applicable

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

sample app

Not applicable

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.

mjtaft2017
Not applicable

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 valuesPrice.PNG

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.PNG

[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
Not applicable

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 .

Not applicable

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.

mjtaft2017
Not applicable

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.

dwforest
Not applicable

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

Not applicable

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