topic Set Analysis: How to filter all previous years in QlikView

Set Analysis: How to filter all previous years

Tue, 25 Jan 2011 16:35:43 GMT

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

Set Analysis: How to filter all previous years

Tue, 25 Jan 2011 16:41:20 GMT

Hi,

try something like this:

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

Set Analysis: How to filter all previous years

Tue, 25 Jan 2011 17:35:40 GMT

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

Set Analysis: How to filter all previous years

Tue, 25 Jan 2011 17:52:55 GMT

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!

Set Analysis: How to filter all previous years

matt_crowther — Tue, 25 Jan 2011 17:56:08 GMT

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

Set Analysis: How to filter all previous years

matt_crowther — Tue, 25 Jan 2011 18:05:25 GMT

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

Set Analysis: How to filter all previous years

Wed, 26 Jan 2011 19:46:53 GMT

Thank you both for greate help