Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Set Analysis: How to filter all previous years

Hi,

try something like this:

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



View solution in original post

6 Replies
Highlighted
Not applicable

Set Analysis: How to filter all previous years

Hi,

try something like this:

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



View solution in original post

Highlighted
Not applicable

Set Analysis: How to filter all previous years

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

Highlighted
Not applicable

Set Analysis: How to filter all previous years

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!





Highlighted
Luminary
Luminary

Set Analysis: How to filter all previous years

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

Highlighted
Luminary
Luminary

Set Analysis: How to filter all previous years

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

Highlighted
Not applicable

Set Analysis: How to filter all previous years

Thank you both for greate help