Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlikuserkv
New Contributor III

Set analysis - from the beginning of the year to the max date

Hello ,

I have a field month with numbers(1,2,3,4.. ) I mean ,1st is January, 2nd is February etc

When I write Month={'>=1 <=3'} in the set analysis, it shows me the results from January to March. How can I write the range if I want to start from January and to have the results before the max(date) information. 

Thank you,  in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: Set analysis - from the beginning of the year to the max date

Try this

Sum({<Month = {"$(='>=1<=' & Num(Month(Max({1}Date))))"}, Year>} Measure)

26 Replies
hoangvvo
New Contributor III

Re: Set analysis - from the beginning of the year to the max date

just a quick guest but something like this? month={">=$(=num#(month(yearstart(now()))))<=$(=num#(month(now())))"}

JustinDallas
Valued Contributor

Re: Set analysis - from the beginning of the year to the max date

You may want to consider using  date oriented flags if this type of calculation is important (and obnoxious).  Generally, such flags reside on a Master Calendar, but even if they are on your Fact and Detail tables, they can provide great value.

qlikuserkv
New Contributor III

Re: Set analysis - from the beginning of the year to the max date

It doesn't work

I have data until the February 1 2018. I want to show the values from the 1st January to the February 1. I also compare other years using this formula ------- sum({$ <Year,Month={'>=1 <= 3'}>})>} Sales) -- this calculates me January February and March Sum in all of the years. I want to change it such way  that it would show me the values until February 1

agigliotti
Honored Contributor II

Re: Set analysis - from the beginning of the year to the max date

do you have a master calendar table?

MVP
MVP

Re: Set analysis - from the beginning of the year to the max date

May be this

Month = {"$(='>=1<=' & Num(Month(Max(Date))))"}

chinnuchinni
Contributor III

Re: Set analysis - from the beginning of the year to the max date

just clarify me ,you want to compare the values from jan to feb only in all years ?

qlikuserkv
New Contributor III

Re: Set analysis - from the beginning of the year to the max date

Yes

qlikuserkv
New Contributor III

Re: Set analysis - from the beginning of the year to the max date

Yes

agigliotti
Honored Contributor II

Re: Set analysis - from the beginning of the year to the max date

so should you use this in your set analysis expression:

Your_date_field = {"<=$(=Max(Date))"}