topic Re: Current year until last month vs. last year until last month in App Development

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

pascaldijkshoor — Mon, 02 Jul 2018 10:26:09 GMT

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

DavidŠtorek — Mon, 02 Jul 2018 10:47:51 GMT

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

pascaldijkshoor — Mon, 02 Jul 2018 10:59:51 GMT

Thanks for your reply but the formula does not work

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

DavidŠtorek — Mon, 02 Jul 2018 11:08:50 GMT

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

pascaldijkshoor — Mon, 02 Jul 2018 11:13:35 GMT

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

DavidŠtorek — Mon, 02 Jul 2018 13:14:03 GMT

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

pascaldijkshoor — Mon, 02 Jul 2018 14:20:19 GMT

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