Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

slribeiro
New Contributor III

Next month (problem when month=12)

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

5 Replies
Highlighted
Partner
Partner

Re: Next month (problem when month=12)

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
New Contributor III

Re: Next month (problem when month=12)

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
Valued Contributor

Re: Next month (problem when month=12)

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

Partner
Partner

Re: Next month (problem when month=12)

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
Valued Contributor

Re: Next month (problem when month=12)

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)

)