topic Re: Set Expression - Force Exclusion for ONE Current Selection in QlikView

Set Expression - Force Exclusion for ONE Current Selection

alistairross — Tue, 12 May 2015 10:12:19 GMT

Hi,

I am trying to create a metric that ignores the current selection in just one field, the other current selections need to still apply to the metric.

The issue is that 'LEDGER_ZERO_DATE_KEY = ' doesn't seem to be making a difference where as the help files suggest that this should clear the selection in the field for the calculations. The full expression is below.

SUM(AGGR(MAX({$<LEDGER_ZERO_DATE_KEY = ,INVOICE_DATE_KEY = {"<= 42004 >= 41640"}, CASH_SUSPENSE_KEY = {2} >} CLIENT_AMOUNT),TRANSACTION_KEY))

The current selection in Ledger_zero_date_key is used to improve performance but this metric needs to ignore it.

Can some one either correct my expression, or tell me why this is not doing what I expect.

Thanks!

Re: Set Expression - Force Exclusion for ONE Current Selection

rubenmarin — Tue, 12 May 2015 10:19:10 GMT

Hi Alistair, maybe works adding set analisys to the first sum, you can use set analisys only in the Max() when you want restrict data, usually it gives the same result, but if you want to ignore user selections wich will affect the Aggr Dimension, you need to add it in the function outside the aggr(), ie:

SUM(({$<LEDGER_ZERO_DATE_KEY =>} AGGR(MAX({$<LEDGER_ZERO_DATE_KEY = ,INVOICE_DATE_KEY = {"<= 42004 >= 41640"}, CASH_SUSPENSE_KEY = {2} >} CLIENT_AMOUNT),TRANSACTION_KEY))

Also note that if you're using this on chart, the dimensions can affect the expression.

Re: Set Expression - Force Exclusion for ONE Current Selection

morganaaron — Tue, 12 May 2015 10:19:23 GMT

Hi Alistair,

Try excluding it from outside the AGGR statement as well, so:

Sum({<LEDGER_ZERO_DATE_KEY>}AGGR(MAX(....

Re: Set Expression - Force Exclusion for ONE Current Selection

alistairross — Tue, 12 May 2015 10:28:22 GMT

Thanks Aaron‌ and RubenMarin‌, this now works as expected.