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: 
rittermd
Master
Master

Limit Dimensions to Last 5 Years

I am trying to limit the bars in my chart to only show the last 5 years.

I have coded this set analysis.  But something is off and not working and I am just not seeing it.

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year]={">=$(=vCurrentYear-5)"}>}EmployeeID)

vCurrentYear = 2017

I got this set analysis to work.  But it is not what I ultimately want.

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year] = {">=$(=date(today()-2100))"} >} Employee_ID)

21 Replies
rittermd
Master
Master
Author

Here is what I get with your expression and many that I have tried

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year]={">=$(=(vCurrentYear)-5)"}>}Employee_ID)

Capture.PNG

Here is what I get with the expression that worked but not what I want

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year] = {">=$(=date(today()-2100))"} >} Employee_ID)

Capture2.PNG

And If I don't use any set analysis I get bars going back to 1990.

sunny_talwar

How do you define vCurrentYear? Is it something like this?

=Year(Today())

rittermd
Master
Master
Author

Yep

sunny_talwar

I don't see anything wrong with what you have + I know if I will create a sample, it will work for me... its something really small that we might be missing, but difficult for me to know without having a sample to look at.

May be someone else can offer there help

rittermd
Master
Master
Author

I don't see anything either. 

Thanks for trying.

sunny_talwar

Hold on a second here

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year] = {">=$(=date(today()-2100))"} >} Employee_ID)

here you are comparing date with year? is [Hire_Date_Most_Recent.autoCalendar.Year] date or year field? from this set analysis it looks like a date field.... I think you need to check this field and figure out what is going on. I would have not expected the above expression to have worked

Anonymous
Not applicable

I'm not sure, if derived fields work in combination with set analysis , even with the newest version of QlikSense available now...

sunny_talwar

But Mike mentioned that this worked, how come this is working?

Count({$<[Hire_Date_Most_Recent.autoCalendar.Year] = {">=$(=date(today()-2100))"} >} Employee_ID)

Is it working on the underlying date field?

Anonymous
Not applicable

I don't know, I just wanted to add a site note.

@all: if it really works, please upload a working sample for me

(tried it here without any success: Set analysis in pivot table)

sunny_talwar

Well he posted an image which seems to be working, but my guess is that it is working on the underlying field. Just as you can create a new field using an expression on the front end, but for the set analysis purposes, you still use the underlying field, it seems that auto-generated fields behave the same way.