How to use previous Year and Current year Volumes as two different expression in Chart (year is not selected or current year value)

[#1]

Hi All,

I'm trying to sum up the Volumes for previous Year and Current year. I must use a chart (ex. Bar Chart) and year as a dimension, totalvolume as first expression, previousyeartotalvolume as second expression. I don't want to solve this calculation in ScriptPage (I've solved as adding a same table ) or any other way (i dont select any year value and only i'm comparing year trend )

QVDTABLE:

ID Year Volume

1 2007 100

2 2007 120

3 2008 90

4 2008 210

5 2009 80

6 2009 120

7 2009 30

Here's the table (as chart) i want to reach:

Year	TotalVolume	PreviousYearTotalVolume
2007	220	0
2008	310	220
2009	230	310

Is it possible ?

Thanks

[#2]

Your first formula is clear:

sum ( Volume)

and then you must work with set analysis.
second formula:

sum({<Year ={Year-1}>} Volume)

I hope I could help you.

Greeting Armin

[#3]

Sorry, but it doesn't work. if you would try to calculate in chart expression, you will see error message.

Thanks

[#4]

Hello,

try Dimension = Year (Sorted Asc)

expression Current = Sum(Volume)

expression Previous = above(Sum(total<Year> Volume))

Hth,
thilo

[johnw · Accepted solution]

Set analysis doesn't work because the set is analyzed once for the entire chart, not once per row. For a simple chart, using above() or below() might be good enough. For more complex needs, I'd solve it with a linkage table:

AsOfYear Type Year
2007 Current 2007
2007 Previous 2006
2008 Current 2008
2008 Previous 2007
2009 Current 2009
2009 Previous 2008

See attached.

[#6]

Thanks to everyone, especially dear John

linkage table solution is very nice, i think it works correctly complex calculation. I've already given an illustration as few records. My database is so complex and huge.

[luismadriz]

Elegant solution!

Thanks