Finding Price Change

[ashwath26]

I have a requirement as follows 

A customer buys product XYZ every month since Jan 2018 till Dec 2018 at $100 every month

I need to find if during any month there has been a price change for the XYZ product he has bought AND BY HOW MUCH % THE CHANGE HAS BEEN FOR. 

E.g. If in June he bought it at $120 then i need to highlight that particular cell in the Pivot Table and show there has been a change of 20%

[Mark_Little]

Hi,
It is going to depend on your data, but probably something like the below.

NoConcatenate

TEMP:
LOAD
*
Resident DATA
ORDER BY CUSTOMER,PRODUCT,DATE asc;

NoConcatenate

CHECKING:
LOAD
*,
IF(CUSTOMER = PREVIOUS(CUSTOMER) AND PRODUCT = PREVIOUS(PRODUCT) AND COST <> PREVIOUS(COST),
1) AS PriceChange,
IF(CUSTOMER = PREVIOUS(CUSTOMER) AND PRODUCT = PREVIOUS(PRODUCT) AND COST <> PREVIOUS(COST),
PREVIOUS(COST)) AS OldChange,
IF(CUSTOMER = PREVIOUS(CUSTOMER) AND PRODUCT = PREVIOUS(PRODUCT) AND COST <> PREVIOUS(COST),
COST) AS NewChange
Resident TEMP;

Then you will have all the information needed. A flag to pick up where the change in price happen, the old and new price to calculate the change.

Mark

[ashwath26]

Hey Mark

Thank you for the reply.
Pls refer the image I have attached , that is the output I am expecting

[ashwath26]

Also the comparison needs to do not just with precious row but all the rows above.

Example in Jan price is $100,
Feb $100 ,
March $100,
April $120

So April record needs to compare with Jan value which is the FIRST VALUE/REFERENCE VALUE

From this need to find , percentage difference for April from reference value