Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

zbeauchemin
New Contributor III

Getting Very Complex Expression to Ignore All Filters in Set Analysis

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 )

)

2 Replies

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

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
New Contributor III

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

Amazing! that worked Thank you Ruben! 

Community Browser