topic Re: If statement in calculated dimension breaks the selection in QlikView

If statement in calculated dimension breaks the selection

amien — Fri, 03 Mar 2017 23:05:43 GMT

I have a table with conditionals shows.

The enduser can select which dimensions he wants to view.

To make it possible i have a listbox (data island) where selections can be made. This works fine.

But now i want to make it more end-user friendly (in the end solutions there are alot of fields to select), i want to split the listboxes.

I do this by adding a calculated dimension to the listbox =If(Tag = 'Order',Field)

The problem is then that is it NOT possible to select a field in both listboxes. So it's not possible to select a field in the client list and a field in the order list.

How can i solve this?

I tried to create a data-island for both client and orders. This will work, but the thing is that i need to proper sort order of the dimensions. And this is a mix of both client fields and order fields.

See attachment ! Thanks in advanced

Re: If statement in calculated dimension breaks the selection

kuba_michalik — Sat, 04 Mar 2017 14:48:28 GMT

You can solve it with some Alternate States trickery (see attachment). It is a bit elaborate, though, maybe someone else has a better idea (something relying on the order of dimensions as defined in the table would be a little simpler, but I take it you want the order to be fully controlable by the #Sort value, so it wouldn't cut it).

Please note it is not possible to have the Current Selections box work with it (Current Selections cannot show selections combined over multiple states)

Re: If statement in calculated dimension breaks the selection

sunny_talwar — Sat, 04 Mar 2017 18:01:27 GMT

I tried to create a data-island for both client and orders. This will work, but the thing is that i need to proper sort order of the dimensions. And this is a mix of both client fields and order fields.

I think might be a good approach, I wonder what issue were you having with the proper sort order. Would you be able to expand on this?

Re: If statement in calculated dimension breaks the selection

amien — Sat, 04 Mar 2017 18:02:53 GMT

Yes, i tried this, but i need all the selections in the Current SelectionBox

Re: If statement in calculated dimension breaks the selection

sunny_talwar — Sat, 04 Mar 2017 18:04:34 GMT

May be use a current selection box alternative to show selections in both inherited and alternative state:

Current Selection Alternatives

Re: If statement in calculated dimension breaks the selection

amien — Sat, 04 Mar 2017 18:09:51 GMT

Because of this string in the dimension :

=$(=('[' & Trim(SubField(Concat(Field,'|',#Sort),'|',1))) & ']')

here is where i use the sort thing. So the first Field should be used for Dim1 in the chart. And the second field becomes Dim2. All based on the sort order. The thing is that Dim1 can be something from the Client table. While Dim2 can be in the Order Table. Therefor i need the Sort field in 1 table.

If you can create a String with all the selected field in AND the right sort order, what will be fine too.But i can only use 1 #Sort field in the Concat. #Sort is a number btw.

Re: If statement in calculated dimension breaks the selection

amien — Sat, 04 Mar 2017 18:12:47 GMT

This might work, but not ideal, because i need to have like 25 alternate startes. It's not only Orders and Client, but alot more categories.

Re: If statement in calculated dimension breaks the selection

sunny_talwar — Sun, 05 Mar 2017 01:30:25 GMT

May be if you want the dimensions to show up in a specific order, you can try using this technique may be?

Re: If statement in calculated dimension breaks the selection

amien — Sun, 05 Mar 2017 08:39:14 GMT

no. . because i have like 500-700 dimensions .. which makes it impossible to maintain

Re: If statement in calculated dimension breaks the selection

Gysbert_Wassenaar — Sun, 05 Mar 2017 11:12:44 GMT

Are you trying to do something like this:

Customisable Straight (and Pivot) Tables - more food for thought‌

User Customisable Data View with Interactive Sort Order

Re: If statement in calculated dimension breaks the selection

amien — Sun, 05 Mar 2017 11:59:48 GMT

yes!

Its basicly the samen solution. The NorthWind solution give order in the Dimension selections using a treeView. i want the same thing, but the with separate listboxes (which i can put in a container).

Re: If statement in calculated dimension breaks the selection

Gysbert_Wassenaar — Sun, 05 Mar 2017 17:55:10 GMT

I think you'll have to combine it with this technique of using alternate states then: Using Alternate States to Select Multiple Values in a List Box with Different Attributes

Re: If statement in calculated dimension breaks the selection

kuba_michalik — Mon, 06 Mar 2017 09:00:50 GMT

Not necessarily impossible.

You could write a macro that would remove all dimensions from the table, then add them back in order specified in the data, with the proper show/hide conditions referring to an appropriate data island, based on value of the Tag field.

This would not be a user-facing macro - only the developer would have to run it when the dimension sort order was changed, so most of the usual caveats about using macros in Qlikview would not apply. If you don't have a specific requirement about rearranging the dimensions by updating e.g. a spreadsheet, without any developer input, then this could be a pretty maintainable solution. But then again, I'm not sure how well would a table with 700 dimensions work, even if 99% of them were conditionally disabled 😕

Re: If statement in calculated dimension breaks the selection

kuba_michalik — Mon, 06 Mar 2017 09:30:30 GMT

And here's my third and (I think) final brilliant idea

Summary: data islands, use set analysis to merge selections in data islands into a selection on the main Field field

Re: If statement in calculated dimension breaks the selection

amien — Mon, 06 Mar 2017 09:34:46 GMT

@Jakub .. that is indeed brilliant

Thanks