set analysis

[#1]

Hi All,

I have a problem with my calculations.

Please take a look @ the attached and let me know how to calculat the $(previous) .

It should be the $ for the line above previous column.

Thanks

[swuehl]

You could try with chart inter record functions like above():

=above( EXPRESSIONUSEDFOR$ )

This should give you the expected result (except for the first month, do you have data for Dec 2009)?

[#3]

Your expression is correct and I did the same thing, but for the first month of each year it returns nothing and I donlt kow why !

Thanks

[swuehl]

You just don't have an 'above' row for that row. Have you limited your rows using a selection in Year?

try

=aggr(above(total sum({1} Value)),Year,Month)

[#5]

I have the above for the first line. Please check the new attached (test.xlsx) to give the idea.

Thanks

[swuehl]

Try

=sum(aggr (Above(total Sum({1} WEIGHT)/2000),Year,Month))

[#7]

try using one dimension, by changing ur Year and Month dimension into MonthYear dimension..

See attached file.

Hope helps,

David Sugito

Business Intelligence Developer

[#8]

Thanks David,

But it's not what I'm looking for.  I don't want to create a data island and also my client wants to keep the presentation as it is.

Any idea is appreciated.

Thanks

Nima

[swuehl · Accepted solution]

Have you tried what I posted above?

In your last posted file, you don't have any data prior 2010, so I am not sure where the above() value for Jan 2010 shown in your firts posted file comes from.

I have attached a demo with some data (assumed that this comes close to your situation) and with a chart demonstrating what I was talking about before.

Regards,

Stefan

[#10]

Thanks  Swuehl,

You are right , I had limited the year that's why I was not able to see the first month. your calcul worked perfectly.

Cheers,

Nima