Set analysis in a simple table: How to do refer to chart dimensions?

[andreas_koehler]

Hi Guys,

I need to compare annual data and wanted to have a simple table with the sum(Amount) of each year and the sum of last year, while the year is one of the chart dimensions.

I tried to calculate the sum of each last year for each year in the dimension as

sum({$<Year= {"$(=$(Year)-1)"}>} Amount)

It does not work. But why and what would be right solution?

I have attached a small example to your convience.

Thanks,

Andreas

[maxgro · Accepted solution]

maybe with this expression

above(sum(Amount))

[vinieme12]

Rangesum(Sum(Amount),above(sum(Amount)))

[sunny_talwar]

Or this if you plan to make selections

Above(Sum({<Year>}Amount)) * Avg(1)

Without Selection

With Selection

[andreas_koehler]

Thanks Massimo,

I had forgotten this expression. Your was the first answer that resolved the initial problem.

Selecting years is a problem though.

Andreas

[andreas_koehler]

Thanks Sunny,

Above(Sum({<Year>}Amount)) * Avg(1)

So by adding the "empty expression" of Avg(1), for which the selection"year" is not disregarded, the whole expression becomes filtered by the selection again?

Nice trick.

Thanks

Andreas

[sunny_talwar]

Yes sir...

[andreas_koehler]

Hi Guys,

thanks for all your help.

Please find an updated version of the example with all the answers side-by-side.

[sunny_talwar]

Are you just compiling and sharing our responses or is there a question for us ?

[andreas_koehler]

Just sharing. My attempt to give something back to the community.