Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuserkv
Contributor III
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

26 Replies
sunny_talwar

Did you get to try this?

chinnuchinni
Creator III
Creator III

if you want to compare only Jan & Feb data you can use below expression.

for currenr / max Year

=sum({<Month1={'>=1 <=2'},Year= {'$(=max(Year))'}>}Sales)

for previous year

=sum({<Month1={'>=1 <=2'},Year= {'$(=max(Year)-1)'}>}Sales)

qlikuserkv
Contributor III
Contributor III
Author

It is exactly what I wanted, but when I select year in the filter, it shows me nothing

sunny_talwar

May be you need to ignore selection in Year field?

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

or this

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

or this

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

qlikuserkv
Contributor III
Contributor III
Author

It works But I don't want to compare to 2 , because  if I add data until the march it should change the graph by itself and I shouldn't need to change the formula

qlikuserkv
Contributor III
Contributor III
Author

I used the first, but now when I select the previous years, it shows me the sum of the whole year values. Can I somehow have only the period from January to max month ??

sunny_talwar

Try this

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

qlikuserkv
Contributor III
Contributor III
Author

Thanks !!!!!!!!!!!!!!!!!! That's what I wanted !!!

qlikuserkv
Contributor III
Contributor III
Author

Can you tell me how can I use this formula in bar chart, if I have dimension month and I want to compare months in these years ? If I have max month information in April, it would show me the January, February, March and April in all of these years

sunny_talwar

When you say these year... you mean the max year or all years?