Showing % change by year in a straight table column when year is already a dimension

[#1]

Hi,

I would like to create a static straight table that has three columns:

1) Year

2) Revenue

3) % change in revenue when compared to prior year data (or "same time last year")

The first two columns are easy to display since year and revenue are separate columns in the database table that I am using.  I have "Year" set as a dimension for the first column, and I use the following QlikView equation for the second column: =SUM(Revenue)

I know set analysis would have to be used for this third column, but I am having a difficult time trying to retrive the prior year data in each row.  The equation I would like to use is: ([Revenue Current Year] - [Revenue Prior Year]) / [Revenue Prior Year]  ...but everytime I try to recreate [Revenue Prior Year] in QlikView I keep getting a 0 value.

Please assist.

Note: There is no year selection list box since I only want to show this table for reporting purposes only.

[#2 · Accepted solution]

It sounds like that one of the Chart Inter Record functions will give you what you are looking for.

Add another expression to your straight table and use Above(Sum(Revenue)) . This will give you the result from the previous record so that you can compare it to Sum(Revenue) and get the % change.

Depending on the look of your Straight Table (or Pivot Table might be even better in this case) try out these 4 Inter Record functions:

• above()
• below()
• before()
• after()

Ingo.