topic Re: Set analysis help in App Development

Set analysis help

Coombsie — Wed, 12 May 2021 08:52:38 GMT

Can someone help me with the set analysis required in a bar chart measure please?

If I have data similar to the data below and I’m showing a bar chart with sales volumes plotted against managers. What I need to happen is if a chart is filtered to one of the managers then the list of included people is filtered down to their team. So anyone who falls under the next level up of hierarchy. I can’t hard code any names in as the actual data covers hundreds of managers.

Senior managers	Managers	Employees	Sales vols
Senior1	Manager1	Person1	4
Senior1	Manager1	Person2	15
Senior1	Manager1	Person3	55
Senior1	Manager1	Person4	21
Senior1	Manager2	Person5	34
Senior1	Manager2	Person6	9
Senior1	Manager2	Person7	14
Senior1	Manager2	Person8	32
Senior2	Manager3	Person9	44
Senior2	Manager3	Person10	43
Senior2	Manager3	Person11	11
Senior2	Manager3	Person12	51
Senior2	Manager4	Person13	6
Senior2	Manager4	Person14	18
Senior2	Manager4	Person15	29
Senior2	Manager4	Person16	31

e.g. If Manager1 is selected in a filter then the chart will show results for Manager1 and Manager2 as they both report into Senior1 but Manager3 and Manager4 will be filtered out.

Re: Set analysis help

GaryGiles — Wed, 12 May 2021 10:40:47 GMT

The following expression should support your desired behavior. Managers=, will cause Qlik to ignore the filter on Managers and the P() function in [Senior managers]=P([Senior managers]) will cause Qlik to filter on Senior managers that are possible given the current filters.

=sum({$<Managers=,[Senior managers]=P([Senior managers])>} [Sales vols])

Re: Set analysis help

tresB — Wed, 12 May 2021 10:50:28 GMT

Try expression:

Sum({<Managers,[Senior managers]=p([Senior managers])>}[Sales vols])

Re: Set analysis help

Coombsie — Wed, 12 May 2021 11:15:12 GMT

Thanks. This works a treat. I had missed off the first = so I was filtering down to the individual manager