Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
alistairross
Partner - Contributor III
Partner - Contributor III

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
rubenmarin

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.

View solution in original post

3 Replies
rubenmarin

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
Specialist
Specialist

Hi Alistair,

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

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

alistairross
Partner - Contributor III
Partner - Contributor III
Author

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