topic Re: Use Variable as Dimension in QlikView

Use Variable as Dimension

Tue, 09 Aug 2011 16:58:55 GMT

I want to create a variable that lists out several field options that would be incorporated into a table chart that would allow the user to change the dimension by changing the variable selection. I want to avoid using an if statement as a calculated dimensior for performance reasons.

Use Variable as Dimension

its_anandrjs — Tue, 09 Aug 2011 17:18:18 GMT

Hi kcambell,

As i understand you want to create a list which holds different dimension list and when you select it. Then you have to analysis pertecular data am i right. If you have any sample then provide me.

Regards,

Anand

Use Variable as Dimension

johnw — Tue, 09 Aug 2011 18:03:59 GMT

I'd just use a cyclic field group.

It's easy to do what I think you're asking for, though. Create a variable like vField. Use $(vField) as your chart dimension. If you want to limit the users to specific fields and give them a drop down, say, go to the constraints tab of the input box, select "predefined values in drop-down", checkmark "listed values", and then list your field names separated by semicolons in the box.

Use Variable as Dimension

Tue, 09 Aug 2011 18:25:37 GMT

I have tried what you suggested, but all it does is list out the variable selection instead of the data that would belong to that field name.

Re: Use Variable as Dimension

johnw — Tue, 09 Aug 2011 18:43:03 GMT

It works fine for me. See attached, which also compares it to using a cyclic dimension, my suggested approach.

Edit: It looks like you probably forgot the dollar sign expansion, $(). If I leave that off, I see just the field name, not the field values.

Re: Use Variable as Dimension

Tue, 09 Aug 2011 19:00:29 GMT

Thank you! I was missing the dollar sign. Any suggestion on how to get field names to display in the drop down without the brackets around it and still get it to still work as a field selection?

Re: Use Variable as Dimension

johnw — Tue, 09 Aug 2011 19:16:51 GMT

Interesting! I'd thought you could list the field without the brackets, and it would work, but it doesn't. Makes sense in hind sight. Since we have an = sign in the dimension, the dimension ends up like =Customer Name, which isn't valid. Easily fixed, though. Leave the brackets off in the drop down, then add them in the dimension:

=[$(vField)]

Fixed in the attached.

Re: Use Variable as Dimension

Tue, 09 Aug 2011 20:36:09 GMT

That worked perfectly!!! Thank you so much!

Re: Use Variable as Dimension

Mon, 15 Aug 2011 04:54:44 GMT

Hi,

Thanks kcampbell to have initiated such topic, I was also interested in such behavior! In fact, I have few reports which look alike, the only difference is only on one or two dimensions. So I wanted to avoid duplicating report and play with a variable to show/hide them.

I have an extra request to your initial one, which is to add an 2nd field, which remain optional. I have checked the option "Suppress When Value Is Null" for the 2nd field, but the pivot table do not render any row if no value is selected for the 2nd field.

Is it consider as a bug? Or may be I don't understand the meaning of the option "Suppress When Value Is Null"

I have modified John's file as per my requirement.

Re: Use Variable as Dimension

johnw — Mon, 15 Aug 2011 18:16:19 GMT

So far as I know, you can't really hide a dimension column in a pivot table that way. I think you would have to play with the display condition on two different charts, one with one dimension, one with two dimensions. Alternatively, you could step up to a full macro implementation of dynamic charting. See attached.

I'm told that version 11 has conditional enable of dimensions and expressions. This will likely be a better way to handle dynamic charting than macros, but I haven't poked at version 11 yet. Our shop is still on version 9.

Re: Use Variable as Dimension

Mon, 15 Aug 2011 19:55:12 GMT

I also had to do a second dimension that depended on the first dimension selection. In order to accomplish this, I used a calculated dimension to say if the first dimension selected is X, then this second dimension, else null. Then under the presentation tab, I did a conditional show for only when that first dimension is selected. If this doesn't make sense, I will try to upload an example.

Re: Use Variable as Dimension

johnw — Tue, 16 Aug 2011 00:06:11 GMT

Ah, yes, I think I understand what you're saying. That should at least work for straight tables, though you can't hide dimensions in pivot tables. I'm not sure why I'm thinking about pivot tables, though, when the example was a straight table. So yeah, that should work.

I didn't do exactly what you said, but using the same basic idea of hiding the dimension, I updated the two variable example. If you select a value for the second variable, it shows the dimension, else it doesn't. Works great. Two cyclic dimensions are shown for comparison. In that case, the dimension doesn't hide, you just choose 'total' and it stops affecting the result.

Re: Use Variable as Dimension

Tue, 16 Aug 2011 01:46:36 GMT

Hi kcampbell,

Why don't you follow the initial idea of this topic? I think you'd better create a second variable vField2, and put your if condition in the definition of this variable

=if(vField='value1','value2')

Otherwise you might encounter some performance issue. If you code this in the Calculated Dimension, i think it will get evaluated for each record.

I had such experience, that is why I came accross your topic

Re: Use Variable as Dimension

Tue, 16 Aug 2011 01:58:41 GMT

Hi John,

Sorry for the confusion. Indeed, in the example it is a straight table, but my requirements are pivots. Sorry...

I mentioned "pivot" in my first comment, that might be the reason why it came into your mind

So, as for now, if there is no easy way to hide a dimension into a pivot (such as the straight table), I will choose the alternative with the macro. Thanks!

Re: Use Variable as Dimension

Tue, 16 Aug 2011 03:09:08 GMT

Hi John,

Here is the code, I will keep:

Sub ChartRemoveDimension

set chart = ActiveDocument.getSheetObject("CH13")

set chartProperties = chart.GetProperties

set dimensions = chartProperties.Dimensions

chart.removeDimension dimensions.Count - 1

End Sub

Sub ChartAddDimension

set chart = ActiveDocument.getSheetObject("CH13")

set chartProperties = chart.GetProperties

set dimensions = chartProperties.Dimensions

chart.addDimension "=[$(vDimension2)]"

set chartProperties = chart.GetProperties

if chartProperties.TableProperties.MissingSymbol = " " then

chartProperties.TableProperties.MissingSymbol = " "

else

chartProperties.TableProperties.MissingSymbol = " "

end if

chart.SetProperties chartProperties

End Sub

Looking forward to scrap this code when QV11 is out

Thanks for your help!

Re: Use Variable as Dimension

Tue, 16 Aug 2011 13:40:05 GMT

Hi,

I realize that I need to re-arrange (= promote) my dimension "=[$(vDimension2)]", as when I re-create it, it appear at the end of the list. Any clue how to do that? I would prefer not to remove all dimensions to re-add them later in sequence.

Is there a way to get the "field name" or "expression" of an existing dimension? I only see a way to get the label of a dimension through chartProperties.dimensions(i).Title.v, but what about the defintion of this dimension?

Related topic can also be found here: http://community.qlik.com/message/141253#141253

Thanks in advance,

Re: Use Variable as Dimension

johnw — Tue, 16 Aug 2011 15:51:30 GMT

Select the dimension and then press the "Promote" button.

Re: Use Variable as Dimension

Tue, 16 Aug 2011 16:31:13 GMT

Yes, in that case that works too. Unfortunately, I had a situation where the additional dimensions are dependent on the first dimension selection. For example, user may select a procedure code for the first dimension and in that case I would need to display the corresponding description in the second column.

Re: Use Variable as Dimension

Tue, 16 Aug 2011 16:34:38 GMT

Hi John,

I can't , I need to handle it within the macro. I can't ask the user to promote the optional dimension every time it gets displayed/hidden

Re: Use Variable as Dimension

johnw — Tue, 16 Aug 2011 18:46:44 GMT

And sorry about my comment on the promote button. Somehow I missed where you posted the macro you were using, so I was missing valuable context.

I wouldn't think you would want to combine the macro I had with the idea of using a variable as a dimension. As long as you're going to the trouble of adding the dimension using a macro, then why not add an ACTUAL dimension instead of a variable? You can hold your dimension names in a list box or any of the other structures that QlikView gives you, and it will behave more "normally" from the user perspective. If you want to restrict your users to only two dimensions, I suppose you could. But why not just let them do whatever they want at that point?

I don't know about the macro commands for promoting and demoting dimensions. I'd be searching through the API guide like you.