Qlik Sense App Development

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

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?

6 Replies
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

Contributor

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

Thanks for your reply but the formula does not work

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.

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

Contributor III

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

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

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