3 Replies Latest reply: Oct 11, 2011 3:59 AM by vivek.singh

# Calculate % Change on Pivot Table

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

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

• ###### Re: Calculate % Change on Pivot Table

see the attached file

hope this help

• ###### Re: Calculate % Change on Pivot Table

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