13 Replies Latest reply: Jan 30, 2018 3:31 AM by Jon Erik Lindberg

# Year plus one

I need to display a measure for "2010" as "2011", "2011" as "2012"....etc.

I have tried this formula (with many variations):

Sum({<Year={\$(={Year}+1)} >}my_measure)

but it returns with 0. I would greatly appreciate any assistance.

• ###### Re: Year plus one

put as a dimension ur year; and as a measure:

below(sum(my_measure))*avg({<Year>}1)

• ###### Re: Year plus one

stalwar1 ; guess from who I copied this technique? haha

• ###### Re: Year plus one

I believe you wanted to do this Omar

below(sum({<Year>}my_measure))*avg(1)

Not sure if the OP needs Above() or Below() or After() or Before(). It will depend on the sorting and pivoting of the year dimensions.

• ###### Re: Year plus one

can't we force the aggregation to be by year ascending? directly in the expression itself?

maybe sthing like:

aggr( below(sum({<Year>}my_measure)) , (Year, ASCENDING))

?

• ###### Re: Year plus one

We sure can

• ###### Re: Year plus one

If I use this :

aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING)))

It does work; but when I select a Year, it correctly bring the correct values, but the other Years disappear!

How can I use this, and keep all the years even when I select a year?

• ###### Re: Year plus one

Found it :

sum({<Year>}aggr(below(sum({<Year>}Sales)) , (Year,(NUMERIC, ASCENDING))))

• ###### Re: Year plus one

But this expression is only if you have Year as dimension... in case you have more dimensions in the chart, you need to add them to your Aggr() function and make sure to have them before the Year dimension.

• ###### Re: Year plus one

Yes sure !

• ###### Re: Year plus one

because you are using an aggregation to display the sum of sales or something of a year, i thought about a simple solution you can play just on the visualisation add one year for the year like my example below
and the result on two graphs below one on the left without add the +1 and the other with it

• ###### Re: Year plus one

Hi,

if you select 2010 as year you'd like to get Sum( my_measure ) of 2011 ?

• ###### Re: Year plus one

To adapt to ur case Jon Erik:

use a chart, as dimension: Year

as a measure:

sum({<Year>}aggr(below(sum({<Year>}YourMeasure)) , (Year,(NUMERIC, ASCENDING))))

• ###### Re: Year plus one

Thank you very much. It worked fine! Now I must study why it worked... :-)