topic Calculate price variation in QlikView

Calculate price variation

Wed, 24 Sep 2014 12:41:46 GMT

Hi I need to do something very simple, I have to calculate a price variation during the year..

I have:

price1

price2

price3

price4

price5

price6

price7

And I want: (price2-price1)/price1

..(price3-price2)/price2....

(price4-price3)/price3 and so on....

How can I do that in a table? I tried to to the ratio but It doesn't show any value!

Re: Calculate price variation

Gysbert_Wassenaar — Wed, 24 Sep 2014 12:46:16 GMT

Are those different fields in a table or values in one field? If they are values in one field you can try using the above() function. For example with a table that contains a Date field and a Price field you could create a straight table with Date as dimension and use Price/above(Price) - 1 as expression.

Re: Calculate price variation

Wed, 24 Sep 2014 12:49:07 GMT

Are different field in a table

Re: Calculate price variation

Gysbert_Wassenaar — Wed, 24 Sep 2014 12:53:46 GMT

Then you need a different expression for each variation calculation:

Expression1: Price2/Price1 -1

Expression2: Price3/Price2 -1

...etc

Re: Calculate price variation

nagaiank — Wed, 24 Sep 2014 13:09:17 GMT

Does the following script give a clue to solve your problem?

Data:

LOAD RowNo() as ID, * Inline [

Price

];

Final:

NoConcatenate

LOAD ID,Price,

If(IsNull(Peek('Price')),Null(),(Price-Peek('Price'))/Peek('Price')) as Ratio

Resident Data Order By ID;

DROP Table Data;

Re: Calculate price variation

Wed, 24 Sep 2014 13:25:23 GMT

I'm trying to do that but It doesn't shows any value just an hyphen!

Re: Calculate price variation

Gysbert_Wassenaar — Wed, 24 Sep 2014 13:29:50 GMT

Can you post an example document that demonstrates the problem?