topic Next month (problem when month=12) in QlikView

Next month (problem when month=12)

slribeiro — Wed, 28 Oct 2015 09:34:15 GMT

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

Re: Next month (problem when month=12)

jduarte12 — Wed, 28 Oct 2015 14:13:57 GMT

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

Re: Next month (problem when month=12)

slribeiro — Wed, 28 Oct 2015 14:40:12 GMT

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.

Re: Next month (problem when month=12)

cesaraccardi — Wed, 28 Oct 2015 14:55:22 GMT

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

Re: Next month (problem when month=12)

jduarte12 — Wed, 28 Oct 2015 15:42:16 GMT

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.

Re: Next month (problem when month=12)

cesaraccardi — Wed, 28 Oct 2015 15:56:09 GMT

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)

)