4 Replies Latest reply: Mar 3, 2017 10:28 AM by Branislav Bujnak

# Getting Year-Over-Year Results

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

RevYearRevMonthRevCategoryRevAmount
2011JanPaper35000
2010JanPaper30000
2011FebPaper26000
2010FebPaper28000
2011MarPaper16000
2010MarPaper15000

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?

-Rick

• ###### Getting Year-Over-Year Results

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

• ###### Re: Getting Year-Over-Year Results

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

• ###### Re: Getting Year-Over-Year Results

Hi,

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

Regards,

Iyyappan

• ###### Getting Year-Over-Year Results

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.