6 Replies Latest reply: Jul 2, 2018 10:20 AM by Pascal Dijkshoorn

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

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

• ###### Re: Current year until last month vs. last year until last month

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

• ###### Re: Current year until last month vs. last year until last month

Thanks for your reply but the formula does not work

• ###### Re: Current year until last month vs. last year until last month

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

• ###### Re: Current year until last month vs. last year until last month

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

• ###### Re: Current year until last month vs. last year until last month

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

• ###### Re: Current year until last month vs. last year until last month

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