Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks for your reply but the formula does not work
Did you just copy it? It should be only suggestion how to do it not the exact formula.
I copied it and changed the Month field to my (month) date field
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
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