Use Variable as Dimension

[#1]

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. 

[#2]

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. 

[johnw]

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.

[#4]

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

[#5]

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!

[#6]

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!

[#7]

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,

[johnw]

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

[#9]

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. 

[#10]

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

[johnw]

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.