topic Re: Filter data with List Box when the expressions use Set Analysis in QlikView

Filter data with List Box when the expressions use Set Analysis

eadbhard — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I have a line chart where the dimension is the month, and the chart has three expressions:

Expression 1 =Avg({$<Customer={'CUSTOMER NAME 1'}>} On_Time_Pct)

Expression 2 =Avg({$<Customer={'CUSTOMER NAME 2'}>} On_Time_Pct)

Expression 3 =Avg({$<Customer={'CUSTOMER NAME 3'}>} On_Time_Pct)

As you can see, each expression is selecting data for a specific customer. The chart works fine but now I want to add a List Box filter with the customer name.

The field in the "Customer" List Box is the same field used in the expressions.

When I select a customer in the List Box, nothing happens in the chart. I expected the chart to show the data of the selected customer only.

So how can I filter by customer in this chart?

Thanks for any insight!

Re: Filter data with List Box when the expressions use Set Analysis

dplr-rn — Wed, 28 Aug 2019 19:40:01 GMT

Why not have customer as a dimension?

Re: Filter data with List Box when the expressions use Set Analysis

sunny_talwar — Wed, 28 Aug 2019 19:52:58 GMT

I agree with @dplr-rn , but if for unspecified reason you have to use your current approach, then change your expressions to this

Expression 1 =Avg({$<Customer *= {'CUSTOMER NAME 1'}>} On_Time_Pct) Expression 2 =Avg({$<Customer *= {'CUSTOMER NAME 2'}>} On_Time_Pct) Expression 3 =Avg({$<Customer *= {'CUSTOMER NAME 3'}>} On_Time_Pct)

Re: Filter data with List Box when the expressions use Set Analysis

eadbhard — Wed, 28 Aug 2019 20:41:10 GMT

@sunny_talwar Worked like a charm! Thanks a lot.

Re: Filter data with List Box when the expressions use Set Analysis

eadbhard — Wed, 28 Aug 2019 20:54:36 GMT

This approach also work wonders. For other new users like me:

Add the Customer as a Dimension
Leave one Expression only
Remove the Set Analysis query from the expression

Great answer, thanks!