topic Set items on a drop down based on selected item in another drop down in App Development

Set items on a drop down based on selected item in another drop down

QlikyBobby — Wed, 12 Dec 2018 14:47:57 GMT

I am somewhat new to Qlik...

Currently we are setting a list of [Sales Team] to select from in a drop down with:

=aggr(only({1<[Sales Team] -={ 'House', 'Institutional', 'Not Applicable', 'Unknown'}>}[Sales Team]),[Sales Team])

This works and allows us to remove a few unwanted [Sales Team].

But what I need to do now is to filter this list further by only displaying [Sales Team] that belong to a [Sales Manager] that is selected in another drop down.

Was trying to do something like this but it is not working

=if([Sales Manager] = 'Sales Manager 1',
aggr(only({1<[Sales Manager] = 'Sales Manager 1', [Sales Team] -={ 'House', 'Institutional', 'Not Applicable', 'Unknown' }>} [Sales Manager], [Sales Team]), [Sales Team])
, aggr(only({1<[Sales Manager] ='Sales Manager 2', [Sales Team] -={'House', 'Institutional', 'Not Applicable', 'Unknown' }>} [Sales Manager], [Sales Team]), [Sales Team])
)

thank you

Re: Set items on a drop down based on selected item in another drop down

marcus_sommer — Wed, 12 Dec 2018 12:11:53 GMT

Try it with:

aggr(only({1<[Sales Manager] = {'Sales Manager 1'}, [Sales Team] -={ 'House', 'Institutional', 'Not Applicable', 'Unknown' }>} [Sales Manager], [Sales Team]), [Sales Team])

- Marcus

Re: Set items on a drop down based on selected item in another drop down

QlikyBobby — Wed, 12 Dec 2018 15:41:35 GMT

Thanks Marcus...

I added the {} around {'Sales Manager 1'}, that was probably part of the problem but it's still giving an empty list for the dropdown

Stephen

Re: Set items on a drop down based on selected item in another drop down

marcus_sommer — Wed, 12 Dec 2018 15:55:09 GMT

I Just see a syntax error within the calculation because the sales manager isn't added to the aggr-dimensionlist else to the only() - therefore ty it with:

aggr(only({1<[Sales Manager] = {'Sales Manager 1'}, [Sales Team] -={ 'House', 'Institutional', 'Not Applicable', 'Unknown' }>} [Sales Team]), [Sales Team], [Sales Manager])

whereby it will depend on your datamodel if you need the sales manager (or maybe any other dimension) within the dimensionlist.

- Marcus

Re: Set items on a drop down based on selected item in another drop down

QlikyBobby — Wed, 12 Dec 2018 16:28:36 GMT

Thank you Marcus for the expression explanation, it works! I am still trying to wrap my head around the expression formats.

Stephen