topic Re: Calculate % Change on Pivot Table in QlikView

Calculate % Change on Pivot Table

Wed, 05 Oct 2011 18:43:34 GMT

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%

Re: Calculate % Change on Pivot Table

hdonald — Tue, 11 Oct 2011 07:14:09 GMT

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,

Re: Calculate % Change on Pivot Table

SunilChauhan — Tue, 11 Oct 2011 07:33:40 GMT

see the attached file

hope this help

Re: Calculate % Change on Pivot Table

Tue, 11 Oct 2011 07:59:02 GMT

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