topic Re: Finding Price Change in QlikView

Finding Price Change

ashwath26 — Sat, 16 Nov 2024 04:59:01 GMT

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%

Re: Finding Price Change

Mark_Little — Wed, 12 Dec 2018 13:36:21 GMT

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

Re: Finding Price Change

ashwath26 — Wed, 12 Dec 2018 15:30:06 GMT

Hey Mark

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

Re: Finding Price Change

ashwath26 — Wed, 12 Dec 2018 15:33:02 GMT

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