Next month (problem when month=12)

[slribeiro]

Greeting, i want to calculate the values of the previous and the following month and i use this formula for the previous:

If(

Right(Max(Mês) - 1, 2) = '0',

sum({$<Mês = {$(=max(Mês+11))}, Ano = {$(=max(Ano)-1)}, TipoDados={'Orçamento'}>} Valor),

sum({$<Mês = {$(=max(Mês)-1)},Ano = {$(=max(Ano)) }, TipoDados={'Orçamento'}>} Valor)

)

Somehow, i can't manage to get the analogue formula for the next month.

Please help me if you can.

Regards,

Simão Ribeiro

[jduarte12]

Hi,

Does the previous month formula work well with month 12?

The problem you are getting with next month formula occurs only for month 12, or it fails regardless of the month?

Regards,

João Duarte

[slribeiro]

The previous formula works perfectly for every months but it calculates the value of the previous month and i want the opposite formula to calculate the same value but this time for the following month.

[cesaraccardi]

Hi Simao,

In case you have a full date field in your data model you should be able to use the AddMonths() function to calculate previous and following time periods and then you can extract the year and month from the resulting date. This function is smart enough to set January as the following month to December so you won't have to worry about that.

Hope this helps,

Cesar

[jduarte12]

Simão,

Try the following:

If(

Max(Mês)  = 12,

sum({$<Mês = {$(=max(Mês-11))}, Ano = {$(=max(Ano)+1)}, TipoDados={'Orçamento'}>} Valor),

sum({$<Mês = {$(=max(Mês)+1)},Ano = {$(=max(Ano)) }, TipoDados={'Orçamento'}>} Valor)

)

Addmonths() should also work, as it has been said.

[cesaraccardi]

Simao,

You could also do it like this:

If(

     Right(Max(Mês), 2) = '12',

     sum({$<Mês = {1}, Ano = {$(=max(Ano)+1)}, TipoDados={'Orçamento'}>} Valor),

     sum({$<Mês = {$(=max(Mês)+1)},Ano = {$(=max(Ano)) }, TipoDados={'Orçamento'}>} Valor)

)