Showing % change by year in a straight table column when year is already a dimension
I would like to create a static straight table that has three columns:
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.
Note: There is no year selection list box since I only want to show this table for reporting purposes only.