Getting Very Complex Expression to Ignore All Filters in Set Analysis

zbeauchemin — Tue, 14 Nov 2017 16:29:31 GMT

Hello, I am currently working on a difficult set analysis. Basically I want it to get the current YTD, but ignore all filters.

Currently it is returning the correct YTD value but still responding when I select other filters(Ex. Value goes to zero when I select a prior year)

See my current expression below:

sum({1<YTDFlag={'1'}>}

Aggr(

if(ID=1,avg({1<YTDFlag={'1'}>}Field1),

if(ID=2,

(RangeSum(

sum({1<YTDFlag={'1'}>}Field3),

sum({1<YTDFlag={'1'}>}aggr(Sum({1<YTDFlag={'1'}>}Field5)*avg({1<YTDFlag={'1'}>}Field4), IndexB))

)) * avg({1<YTDFlag={'1'}>}Field2)

if(ID=3,

if(

(RangeSum(

sum({1<YTDFlag={'1'}>}Field3),

sum({1<YTDFlag={'1'}>}

aggr(Sum({1<YTDFlag={'1'}>}Field5)*avg({1<YTDFlag={'1'}>}Field4)

, IndexB)

)

* avg({1<YTDFlag={'1'}>}Field2)<avg({1<YTDFlag={'1'}>}Field1)

, (RangeSum(sum({1<YTDFlag={'1'}>}Field3),

sum({1<YTDFlag={'1'}>}aggr(Sum({1<YTDFlag={'1'}>}Field5)*avg({1<YTDFlag={'1'}>}Field4),

IndexB)

)

) * avg({1<YTDFlag={'1'}>}Field2),

avg({1<YTDFlag={'1'}>}Field1)

)

, IndexA )

)

Re: Getting Very Complex Expression to Ignore All Filters in Set Analysis

rubenmarin — Tue, 14 Nov 2017 16:38:56 GMT

Hi Zach, following this syntax you need to ignore selections in all fields, you can use Only() for those outside a function like 'ID':

if(Only({1} ID)=1,avg({1<YTDFlag={'1'}>}Field1),

zbeauchemin — Tue, 14 Nov 2017 19:03:47 GMT

Amazing! that worked Thank you Ruben!