Set Expression - Force Exclusion for ONE Current Selection

[alistairross]

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!

[rubenmarin · Accepted solution]

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]

Hi Alistair,

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

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

[alistairross]

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