Qlik Community

Qlik Sense App Development

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

pascaldijkshoor
Contributor

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
Contributor III

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

pascaldijkshoor
Contributor

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

Thanks for your reply but the formula does not work

DavidŠtorek
Contributor III

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.

pascaldijkshoor
Contributor

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

DavidŠtorek
Contributor III

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

pascaldijkshoor
Contributor

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

Community Browser