0

In a graph I'm trying to show last month with this expression:

=If( [SH_historico_1.ANOMES] >= '$(=Max([SH_historico_1.ANOMES])-1)' and   [SH_historico_1.ANOMES] <= '$(=Max([SH_historico_1.ANOMES]))', [SH_historico_1.ANOMES])

When I write [SH_historico_1.ANOMES] I'm referring to Period, Like 'YYYYMM' And when I subtract -1 I'm just substact 1 to, example: 202002 -1 = 202001, so works, because It will calculate the correct period.

But... If the month it is january? YYYY01? example 202001 -1 = 202000 00 month number it does not exist. The 00 it should be 12.

So I'm wondering what if I treat the number as a date? I'm trying to use Date() function but I'm little stuck.

How it could be with correct syntax?

Another solution I'm thinking it is to set some code in editor code, but still developing the idea.

Any help it is welcome.

Sebastián
  • 437
  • 5
  • 19

1 Answers1

1

When you want to add/subtract months its better to use AddMonths() function: AddMonths( DateField, NumberOfMonths). This function will deal with the January issue

In your case will be: =AddMonths(Max([SH_historico_1.ANOMES]), -1) (as you can see the number of months can be negative number which will subtract the months from the date)

And a bit on advice: If you are planning to use this calculation in expression then consider using Set Analysis instead of if..then..else statement. if statements are slower and they are consuming more resources when used in expressions

Stefan Stoichev
  • 4,615
  • 3
  • 31
  • 51