# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Creator

## 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?

6 Replies
Creator III

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

Creator
Author

Creator III

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

Creator
Author

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

Creator III

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

Creator
Author

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