Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Set Expression - Force Exclusion for ONE Current Selection

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!

1 Solution

Accepted Solutions

Re: Set Expression - Force Exclusion for ONE Current Selection

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.

3 Replies

Re: Set Expression - Force Exclusion for ONE Current Selection

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.

morganaaron
Valued Contributor

Re: Set Expression - Force Exclusion for ONE Current Selection

Hi Alistair,

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

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

Partner
Partner

Re: Set Expression - Force Exclusion for ONE Current Selection

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