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: 
Not applicable

Set Analysis: How to filter all previous years

Hi , I need help to write an expression to sum on Sales by all previous year relative to the selected year. Thank you

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

try something like this:

sum({$<Year={"<=$(max(Year))"}>} Sales)



View solution in original post

6 Replies
Not applicable
Author

Hi,

try something like this:

sum({$<Year={"<=$(max(Year))"}>} Sales)



Not applicable
Author

I have a list box where user can select the Year. When I use your expression the number of Sales that I am getting does not change regardless of the Year selection. Should I use {1} instead of {$} ?

Also why are you sugesting max() function? Thank you

Not applicable
Author

I was missing a =, the expression is:

sum({$<Year={"<=$(=max(Year))"}>} Sales)

I use max in case the user selects more than one year

best regards!





matt_crowther
Luminary Alumni
Luminary Alumni

Attached is a non-Set Analysis solution that works for me - script and expressions should all be reasonably straight forward.

I'm sure it will be possible using Set Analysis but this works fine for me.

Hope that helps,

Matt - Visual Analytics Ltd

matt_crowther
Luminary Alumni
Luminary Alumni

Version using Set Analysis aswell is attached - it doesn't need to use the additional YearSelect field so is probably more suited.

Essentially the solution is:

sum({1 <Year = {'<$(vYear)'}>} total Sales)



With vYear being simply =Year.

All the best,

Matt - Visual Analytics Ltd

Not applicable
Author

Thank you both for greate help