topic Re: Exclude Values from Set Analysis in QlikView

Exclude Values from Set Analysis

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following expression with Set Analysis:

=MAX(IF(AGGR(NODISTINCT MAX(SALE_DATE),STORE)=SALE_DATE,CONTRACT))

Which brings me the last Contract for a Max Date by Store.

I need to exclude 2 values with Set Analysis from field STATUS, values: Restored (capital letter) and restored (lowercase).

Could I exclude both at once? Or do I have to specify each one?

Thank you!!!

sunny_talwar — Wed, 10 Jan 2018 19:01:00 GMT

May be this

=MAX({<STATUS -= {'Restored', 'restored'}>} IF(AGGR(NODISTINCT MAX({<STATUS -= {'Restored', 'restored'}>} SALE_DATE),STORE)=SALE_DATE,CONTRACT))

microwin88x — Wed, 10 Jan 2018 19:08:44 GMT

Thank you Sunny.

Do you think it would be necessary to add the Status filter on both sides? Or it would be Ok with the 1st one?

sunny_talwar — Wed, 10 Jan 2018 19:12:19 GMT

It's safe to add it in both places... but it might work with one... I am not sure... will have to tested out.

microwin88x — Wed, 10 Jan 2018 19:13:10 GMT

Thanks!