topic Re: Calculating Percentage Change in QlikView

Calculating Percentage Change

Fri, 14 Sep 2012 11:17:06 GMT

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

Re: Calculating Percentage Change

Gysbert_Wassenaar — Fri, 14 Sep 2012 11:49:09 GMT

See attached qvw

Re: Calculating Percentage Change

Fri, 14 Sep 2012 13:08:53 GMT

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

Re: Calculating Percentage Change

Gysbert_Wassenaar — Fri, 14 Sep 2012 13:54:19 GMT

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

Re: Calculating Percentage Change

Thu, 29 Nov 2012 16:34:26 GMT

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?

Re: Calculating Percentage Change

Tue, 30 Sep 2014 02:04:32 GMT

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?