Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gwmills_fiserv
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 (2)
3 Replies
Carlos_Reyes
Partner
Partner

 

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.

gwmills_fiserv
Partner
Partner
Author

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.

Carlos_Reyes
Partner
Partner

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.