Calculate % Change on Pivot Table

[#1]

I've got a flat-file structured set of data that shows a bunch of orders, the year the order was placed, and the product type. I've provided an example below. Simplified for sake of discussion.

Order ID	Year	Type
34216	2008	Red
98432	2010	Brown
10293	2009	Brown
12345	2010	Red
67890	2009	Yellow
54321	2010	Brown

Here is the table I want QlikView to produce. I've been able to accomplish this with a Pivot table except for the % change. How would I go about calculating the % change? I'm looking for solutions that either include an expression or hopefully without the pivot table altogether. Using something other than the flatfile with one single year column is not an option. Thank you in advance for your thoughts!

Type	2008	2009	2010	% Change
Red	1	0	1	0%
Brown	0	1	2	200%
Yellow	0	1	0	0%

[hdonald]

Hi,

An expression comparing min and max years for % change would be possible but simpler if rows were added for those years without any orders (see attached).

The idea of using the ColumnNo() to change the expression in the total of a pivot table would also be handy - as shown in this thread http://community.qlik.com/thread/11316. by Daniel Rozental.

If your Change % represents a kind of fact, like Revenue Change %, that you'd want to see if you only selected a single year, you could get the script to work out the change by using Peek() in the load, maybe to a summary table (see second attached for the basic idea).

A cumulative script using Peek() is shown here http://community.qlik.com/thread/31436 by swuehl.

Regards,

HD

[SunilChauhan]

see the attached file

hope this help

[#4]

Hi,

As u have 3 years(2008,09,10),with respect to which year u want the % change

opt1 2008 vs 2010

opt2   2009 vs 2010

opt3   2008 vs 2009

Thanks

vivek