How to calculate the change of a value (e.g. a price)?

[#1]

Following table is given:

Seller | Product | Date | Price

S1 | P1 | 2010-08-02 | 14.99

S1 | P1 | 2010-08-03 | 17.49

S1 | P1 | 2010-08-04 | 15.19

S1 | P2 | 2010-08-02 | 5.69

S1 | P2 | 2010-08-03 | 5.79

S2 | P1 | 2010-08-02 | 13.99

S2 | P1 | 2010-08-03 | 13.49

...

A new column should show the changes of the price.

Seller | Product | Date | Price | Change

S1 | P1 | 2010-08-02 | 14.99 | -

S1 | P1 | 2010-08-03 | 17.49 | 17%

S1 | P1 | 2010-08-04 | 15.19 | -13%

S1 | P2 | 2010-08-02 | 5.69 | -

S1 | P2 | 2010-08-03 | 5.79 | 2%

S2 | P1 | 2010-08-02 | 13.99 | -

S2 | P1 | 2010-08-03 | 13.49 | 4%

...

But how to define the formula? Maybe the inter-record-functions could help!?

[Miguel_Angel_Baeyens]

Hello,

I don't know how do you want to calculate that change, but say its the change between the price of the current line and the previous line. The, the expression would look like

([Price] - above([Price])) / above([Price])

Of course, it depends on your chart pivoted positions and so.

Hope that helps

[#3]

Hello Miguel,

thx for your reply.

The expression you wrote works if seller and product not changes. But when they change no calculation should be done. It is also a problem that it only works when the right sorting is set.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

You have different solutions for that.

One is sort dimensions so they always bi in that order needed for the formula to work. Use the Sort tab in the chart properties for that.

Another is to create conditions

if([Seller] = above([Seller]), ([Price] - above([Price])) / above([Price]))

or something similar, but the more complex your expressions are, the longer it takes to calculate the chart.

Regards

[#5]

Thank you Miguel, this works !

ra117hama

(Anton)