topic Re: Using a field as a set modifier and in a listbox in QlikView

Using a field as a set modifier and in a listbox

Fri, 11 Nov 2016 12:51:47 GMT

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

Re: Using a field as a set modifier and in a listbox

Anonymous — Fri, 11 Nov 2016 15:03:25 GMT

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.

Re: Using a field as a set modifier and in a listbox

johnw — Fri, 11 Nov 2016 20:46:29 GMT

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)

Re: Using a field as a set modifier and in a listbox

Fri, 11 Nov 2016 23:47:58 GMT

Thank you, that works!