topic Getting Year-Over-Year Results in QlikView

Getting Year-Over-Year Results

Wed, 29 Feb 2012 23:34:21 GMT

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

Getting Year-Over-Year Results

CELAMBARASAN — Thu, 01 Mar 2012 05:03:00 GMT

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

v_iyyappan — Thu, 01 Mar 2012 05:27:54 GMT

Hi,

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

Regards,

Iyyappan

Getting Year-Over-Year Results

Thu, 01 Mar 2012 18:06:16 GMT

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.

Re: Getting Year-Over-Year Results

pentaxadmin — Fri, 03 Mar 2017 15:27:30 GMT

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