Calculating Percentage Change

[#1]

Hi

I have a table which looks like this

Item No	Price	Date
1	100	1/1/2012
2	200	1/1/2012
1	150	1/2/2012
2	210	1/2/2012
1	250	1/4/2012
2	170	1/4/2012

I would like to plot the percentage change of the average price from the previous available month and the Year To Date percentage change of the average price so I get a table which looks like this.

	Jan-12	Feb-12	Apr-12
Monthly % Change	0%	20%	16.7%
YTD % Change	0%	20%	40%

Hopefully that makes sense. I don't know if this might be easier if I broke the first table up into 3 tables, 1 for each month, but that would cause me quite a few headaches elsewhere and would then have to edit the script every month as i get new data to include a new table rather than just stick my new data on the bottom of the table. Sounds clear as Mud.

Any help would be greatly appreciated.

Thanks

Richard

[Gysbert_Wassenaar]

See attached qvw

[#3]

Thanks qwassenaar but I only have Qlikview personal edition so I can't open files created by other users. Could you explain how you did it on here?

Thanks

[Gysbert_Wassenaar · Accepted solution]

I loaded the data in a table named Sales

Sales:

load * inline [

Item,    Price,    Date

1,    100,    01-01-2012

2,    200,    01-01-2012

1,    150,    01-02-2012

2,    210,    01-02-2012

1,    250,    01-04-2012

2,    170,    01-04-2012

];

Then I created a pivot table and added a calculated dimension Month: Month(Date) & '-' & Year(Date)

Next I added two expressions:

Monthly % Change: sum(Price) / before(sum(Price)) -1

YTD % Change: sum(Price) / (sum({<Date = {'01-01-2012'} >} total Price)) -1

Last I pivoted the rows and columns so the pivot table looks like what you asked for in your first post

[#5]

Thanks,

That worked a treat.

The only problem I have is in pivoting the table. When I try and drag Monthly % Change and YTD % Change to the left the table freezes with a big X on it and I have to press a button that looks like refresh on most browsers and the table goes back to how it was with Monthly and YTD headers at the top rather than to the side.

Any ideas?

[#6]

we can use Before() or Above() functions, but let say, a pivot table from Jan - Dec, above or before function can apply from Feb to Dec, but any idea how to do for Jan?