Getting Year-Over-Year Results

[#1]

I have a table with revenue amounts for a given year, month and category.  Each record contains the year, month, category and amount. 

RevYear	RevMonth	RevCategory	RevAmount
2011	Jan	Paper	35000
2010	Jan	Paper	30000
2011	Feb	Paper	26000
2010	Feb	Paper	28000
2011	Mar	Paper	16000
2010	Mar	Paper	15000

My sheet has a selection box showing the years in the table and used the field RevYear.  In my pivot table I want to be able to show an additional column with the previous year from the one selected and also a column for the year-over-year variance.

With the year 2011 selected:

For the column label I used the expression (it returned 2011):     RevYear

I used this formula to show the selected year (this returned 77000):  sum(if(RevYear),RevAmount,0) 

For the previous year column label I used the expression (it returned 2010):      RevYear-1

For the previous year column I used (this also returned 77000, it should have returned 73000):   sum(if(RevYear-1),RevAmount,0)

Does anyone have any idea what I am doing wrong?

Thanks in advance for your help.

-Rick

[CELAMBARASAN · Accepted solution]

Hi,

     Make the expressions as below with the use of set analysis.

     Sum({<RevYear={$(=Max(RevYear))}>} RevAmount) for selected year or current year.

     Sum({<RevYear={$(=Max(RevYear)-1)}>} RevAmount) for previous year.

Hope it helps

Celambarasan   

[v_iyyappan]

Hi,

     I attached a sample QV file Hope its useful for u.

Regards,

Iyyappan

[#4]

Thanks so much to Celambarasan and v.iyyappan.  I am very new to Qlikview and didn't know much about set analysis.  This helps alot.

[pentaxadmin]

celambarasan

I need to calculate YoY variance for multiple years

Year:                    Y1   Y2  Y3

Sales:                100  250  200

YoY Variance:        +150   -50

Currently, I am using the set analysis, but it will only calculate current or selected year compared to previous year

sum({$<Year={'$(=max(Year))'} Sales)-sum({$<Year={'$(=max(Year)-1)'} Sales)

thanks,B