Using a field as a set modifier and in a listbox

[#1]

Hi all,

I am using a field as a set modifier to calculate an expression in a chart:

=Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category={'Large Scale Study'}>}RecruitmentCount)+Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category={'Observational'}>}RecruitmentCount)*3.5+(Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category={'Interventional'}>}RecruitmentCount)*11)

This is working fine.

I would also like to have the Category field in a ListBox so that users can limit the data in the chart to one or more particular Category, but I cannot get it working. I understand why the above expression will not change using a selection, but could someone suggest a workaround? I cannot use a drill down group as I need the Dimension to remain as it is. I am also unable to change the data model as the data is coming in externally.

Apologies if there is an obvious solution, I am new to QlikView

[Anonymous]

Your specifying the Category in your Set Analysis though, so any selections will be overruled by that. If you want the expression to be effected, then you'll need to remove that.

[johnw · Accepted solution]

If I've understood, it's simple. You can do an intersection with your set expression and the current selections. Instead of =, use *=.

=Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category*={'Large Scale Study'}>}RecruitmentCount)+Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category*={'Observational'}>}RecruitmentCount)*3.5+(Sum({<RecruitmentDate_FinancialYear={'FY1617'}, Name={'XXX'}, Category*={'Interventional'}>}RecruitmentCount)*11)

[#4]

Thank you, that works!