topic Re: Aggregating breaks searching in filter pane? in App Development

Aggregating breaks searching in filter pane?

Mon, 23 Jan 2017 12:48:43 GMT

Hi,

I'm an intermediate Qlik user. I have been rebuilding our existing dashboard with some new insights and think I may have encountered a bug. Either that, or my logic is flawed. Help would be appreciated.

I've built a filter pane that displays customer names. In the past this would show duplicates if a customer changed it's name. I've solved this by aggregating on customer number. Something like:

Aggr({$<Year={$(=thisYear)}>} Mode(CustomerName),CustomerNumber)

The statement itself works perfectly. I now see the version of the name that occurs the most within the current selection, but now the search functionality of the pane breaks. When I click on a company name, it filters by company number.

Now that's not that big a problem (I'd prefer it if it showed the name, but the result is the same). However, search is actually broken. I cannot search for the company name, nor the company number. As soon as I type anything, it just hides everything, even when I literally type 100202 from the screenshot above.

Is this a (known) bug, or am I just thinking the wrong way?

Update: video demonstrating the behaviour

Updated to add video

Re: Aggregating breaks searching in filter pane?

vinieme12 — Mon, 23 Jan 2017 13:36:33 GMT

no it's not a bug

Aggr({$<Year={$(=thisYear)}>} only(CustomerName),CustomerName)

Re: Aggregating breaks searching in filter pane?

Mon, 23 Jan 2017 13:43:44 GMT

Cheers, the update works, but the filter pane still behaves weirdly. I've switched browser to confirm it isn't just my browser acting up.

Behaviour (with this formula)

1. Searching within filter pane is impossible

2. As soon as I filter something, all other options in filter pane disappear (making it impossible to select multiple)

3. When I click to deselect the green selected option, it doesn't deselect, but it does show the other options again.

I could try to make a video, but all my customer names would be in it, so that's a bit tricky. Let me know if anything is unclear.

Re: Aggregating breaks searching in filter pane?

vinieme12 — Tue, 24 Jan 2017 04:03:36 GMT

Try below

Aggr ({$<Year={$(=thisYear)}>} only ({1}CustomerName),CustomerName)

Re: Aggregating breaks searching in filter pane?

jonathandienst — Tue, 24 Jan 2017 04:55:47 GMT

That's how calculated dimensions and list boxes work.

While you can work around this problem, your best bet would be clean the data during load, so that you find the most used customer name by customer number and create a dimension table containing the distinct customer numbers with the modal name for each. Replace the names with these names back into the fact table, or simply drop the names from the fact table.

Then there is no need for a calculated field in the list box.

Re: Aggregating breaks searching in filter pane?

Tue, 24 Jan 2017 12:28:32 GMT

This turned out to be pretty much right! The code itself didn't work, but this one did.

Aggr ({$<Year={$(=thisYear)}>} Mode({1}CustomerName),CustomerNumber)

The glitch is gone, but I still see the right customer name!

Re: Aggregating breaks searching in filter pane?

Tue, 24 Jan 2017 12:37:43 GMT

Thanks. I understand how the filter part (showing number instead of name) is expected behaviour, but I find it hard to believe that the behaviour seen in the video is meant to be this way. It literally shows checked when it isn't checked and it selects a company different from the one checked.

So far it seems the code above does solve the glitches. It still displays the customer number, but that's not that important.

I may still look in your suggestion to see if it's better in the long run. Thanks!

Re: Aggregating breaks searching in filter pane?

gkluca82 — Wed, 25 Jan 2017 08:13:14 GMT

I've got the same issue.

Using a conditional filter, i was removing the unrelated value from a list. This worked since Sense 3.0.

With 3.1 this workaround doesn't work anymore. It's behaviour is similar to vicvancooten's one.

when i click into the field i got no values...

if i open the filter in the canvas i can see some values...

if i full screen the filter with the top right button i can see the values...

This formula worked before Sense 3.1. It groupby the sum of some values by Ordine_produzione, and, if this values is >0, show me this Ordine_produzione, otherwise null.

It was very simple to implement, but now is completely useless.