Current year until last month vs. last year until last month

[pascaldijkshoor]

Hi guys,

I want to make a comparison for this year until last month vs. last year until last month. So for today that would mean i would compare January - June 2017 with January - June 2018.

I am using the following formula for current year:

Sum ({$<[L_BEGIN_DATE.autoCalendar.MonthsAgo]={">0"},[L_BEGIN_DATE.autoCalendar.YearsAgo]={"0"}>}Revenue)

But I can't figure out how to calculate last year. Who knows the correct formula?

Thanks in advance

[DavidŠtorek]

Hi,

I don't know what exactly is hidden under your fields with dates, but try using it like this

In your expression for this year use Sum ({$<Month={"<Month(Today(1))"},[L_BEGIN_DATE.autoCalendar.YearsAgo]={"0"}>}Revenue)

In your expression for last year use Sum ({$<Month={"<Month(Today(1))"},[L_BEGIN_DATE.autoCalendar.YearsAgo]={"1"}>}Revenue)

Assumintg that field Month is field with month number.

Hope this helps

[pascaldijkshoor]

Thanks for your reply but the formula does not work

[DavidŠtorek]

Did you just copy it? It should be only suggestion how to do it not the exact formula.

[pascaldijkshoor]

I copied it and changed the Month field to my (month) date field

[DavidŠtorek]

Sorry my bad,

set expression should look like this

{$<Month={"<$(=Month(Today(1)))"}>}...this should take into account all months numbers smaller than current month and the second part of your expression, if I understand well, contains flag about last year?

If not than use the similar for year

Year={"$(=Year(Today(1))-1)"}

so it should look like this 

In your expression for this year use Sum ({$<Month={"<$(=Month(Today(1)))"}, Year={"$(=Year(Today(1)))"}>}Revenue)

In your expression for last year use Sum Sum ({$<Month={"<$(=Month(Today(1)))"}, Year={"$(=Year(Today(1))-1)"}>}Revenue)

Now it should be correct

[pascaldijkshoor]

I just found a different way of calculating which works as well. But many thanks for your effort anyway!

The formula I used is:

Sum ({$<MonthNum={"<=$(=Num(Month(today())))"},[L_BEGIN_DATE.autoCalendar.YearsAgo]={"1"}>}Revenue)

With the following formula added in script:

num (month (L_BEGIN_DATE)) as MonthNum