Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

I have a set analysis that I would like to use filters to affect the restults

I have the following SET analysis :

=sum({1<[SCHEDULE LINE DESC]={'7041100 Cons Lns - Annual Fees Ploc','7048100 Comm Non - RE - Annual Fees','7046100 Comm RE Ln - Annual Fees Loc'},[SCHEDULE NAME]={'Income Statement'},[YEAR NAME]={'2017'},[VERSION NAME]={'Actual'},CENTER={'Institution'},PERIOD={'Apr','Aug','Dec','Feb','Jan','Jul','Jun','May','Nov','Oct','Mar','Sep'}>}Value)

I would like to be able to have the YEAR be an item I use a visualization filter to change.  How do I adjust the formula?

Labels (3)
3 Replies
Partner
Partner

Re: I have a set analysis that I would like to use filters to affect the restults

 

First of all, your formula is forcing all data to be used because of the 1 set identifier:

=sum({1<

Review the Set Analysis documentation to get a better understanding on set identifiers but... depending on what you need, you may want to substitute the number 1 for a $, the $ will enable any user selection to be respected in the calculation of your formula.

Also, once you have change that, you'll need to get rid of:

YEAR={'2017'}

This is forcing your formula to use only 2017 data. If you get rid of this code, you'll be able to select any year anywhere in the app and make that year the applied filter for that formula.

Finally, you may want to get rid of the part:

PERIOD={'Apr','Aug','Dec','Feb','Jan','Jul','Jun','May','Nov','Oct','Mar','Sep'}

That way you'll also enable users to select which PERIODS they want to see or use for that formula.

Partner
Partner

Re: I have a set analysis that I would like to use filters to affect the restults

I understand what you are  saying, and it makes sense from my understanding of the documentation, but when I replace the 1 with a $, my result now displays 0.

Partner
Partner

Re: I have a set analysis that I would like to use filters to affect the restults

In that case I'd identify which part of the formula is making it to show a 0. For instance, I'd start with:

=sum({$<[SCHEDULE LINE DESC]={'7041100 Cons Lns - Annual Fees Ploc','7048100 Comm Non - RE - Annual Fees','7046100 Comm RE Ln - Annual Fees Loc'}>}Value)

And see if that formula returns a result different than 0, if so, I'd add the next filter:

=sum({$<[SCHEDULE LINE DESC]={'7041100 Cons Lns - Annual Fees Ploc','7048100 Comm Non - RE - Annual Fees','7046100 Comm RE Ln - Annual Fees Loc'},[SCHEDULE NAME]={'Income Statement'}>}Value)

and again, confirm the result is different than 0, and so... until you can identify which filter inside the set analysis section is making the formula show a 0 result. Once you know which filter is the root cause, you can explore your data to understand if the filters you want to combine make sense or maybe the formula is showing a 0 when the filters you select don't share an association with the filters you use in your formula.