Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
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?

Thanks in advance

6 Replies
DavidŠtorek
Creator III
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

pascaldijkshoor
Creator
Creator
Author

Thanks for your reply but the formula does not work

DavidŠtorek
Creator III
Creator III

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

pascaldijkshoor
Creator
Creator
Author

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

DavidŠtorek
Creator III
Creator III

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
Creator
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