How to make a graph showing only growth over period?

[#1]

Let say I have a database:

Year	Department	Product	Sale
2008	US	Socks	1000
2009	US	Socks	2000
2010	US	Socks	1500
2011	US	Socks	1700
2008	UK	Socks	2000
2009	UK	Socks	2500
2010	UK	Socks	3000
2011	UK	Socks	3100

I want to create a graph showing only the growth for each given year. So the graph would use information like that:

Header 1	Header 2	Header 3
2009	US	1000
2010	US	-500
2011	US	200
2009	UK	500
2010	UK	500
2011	UK	100

As I understand it can be done so that while creating a graph I'll prepare each year expression separately.

But I would want more simple solution as I would have more than 10 years to show and would want to use it constantly.

Would it be possible and effective to great the additional field for each row showing the Last Year Sale also.

For example making resident load of database where 'Year+1 as Year' and then outer joining it back to database.

So when creating a graph I could say that 'Change' would be 'Sale - LastYearSale' or 'Sale/LastYearSale-1' when wanting to show it as a %.

As I have a database with 40 millions rows and 30 dimensions is the outer joining a problem? Is the better solution?

[#2]

Hi,

You can use the below expression to calculate sum(Sales)-Sum(Lastyearsales)

Sum(Sales) - Sum({<Header1={'=$(Header1-1)'}>}Sales)

Replace Header1 with your Year dimension

[perumal_41 · Accepted solution]

Hi

Try this

Sum(Sales) -above(Sum(Sales))

[#4]

Thank you for the quick answer.

sum({<Year={'=$(Year-1)'}>}Sales)  shows zeros for every row for some reason

[mdmukramali]

Dear,

Can you check the attached file.

I hope it will help you.

Thanks,

Mukram

[#6]

Suggested solutions work until I do not include other dimensions. And the formula is getting more complex.

Wouldn't be better to create a extra "LastYearSale" column to database? What are the negative side of it? It should be faster and its should not waste much space? Based on googeling, no-one is suggesting that for some reason...