25 Replies Latest reply: Nov 19, 2014 6:29 PM by Vishnu Sreekumar RSS

    Use Variable as Dimension

      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
          Anand Chouhan

          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
              John Witherspoon

              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

                  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
                      John Witherspoon

                      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

                          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
                              John Witherspoon

                              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

                                  That worked perfectly!!! Thank you so much!

                                    • Re: Use Variable as Dimension

                                      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
                                          John Witherspoon

                                          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

                                            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
                                                John Witherspoon

                                                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

                                                    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

                                                        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

                                                        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
                                                            John Witherspoon

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

                                                              • Re: Use Variable as Dimension

                                                                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
                                                                    John Witherspoon

                                                                    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.

                                                              • Re: Use Variable as Dimension

                                                                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

                                                                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

                                                          • Use Variable as Dimension

                                                            Hi John,

                                                             

                                                            I have an associated problem: I have a complicated cashflow calculation where all the values have two versions, one basic and one including Skonto (rebate), named like this:

                                                             

                                                            [Cash in from  Customers]

                                                            [Cash in from Customers_Skonto]

                                                             

                                                            In charts I want to give the user the choice between the two value-versions, so I set up a variable vSkonto, which can be either empty or '_Skonto', by user-button-click.

                                                             

                                                            The sum should look something like:  sum([Cash in from Customers$(vSkonto)])

                                                            so that the user can control which fieldname is used. The problem is: I can't get it to work syntaxwise. I tried in and out of brackets, with and without quotes - can't you do it that way at all, or am I missing a trick?

                                                             

                                                            I know I could do it with an if-statement, but the formulas are already convoluted if-statements, so I'd prefer not to have to add another layer.

                                                             

                                                            It would be really great if you could help me,

                                                            best,

                                                            Lenka

                                                            • Re: Use Variable as Dimension

                                                              Dear John,

                                                               

                                                              I read your answer and tried the solution that you gave but it does not work.

                                                               

                                                              I have a variable named "vBucket" which is defined as :-

                                                               

                                                              if(($(vPercentage))> 0 and ($(vPercentage))<=25 ,'0-25',

                                                              if(($(vPercentage))> 25 and ($(vPercentage))<=50 ,'26-50',

                                                              if(($(vPercentage))> 50 and ($(vPercentage))<=75 ,'51-75',

                                                              if(($(vPercentage))> 75 and ($(vPercentage))<=100 ,'76-100','Above 100'))))

                                                               

                                                              Now the "vBucket" variable works perfectly fine in textbox but not in the pivot table as a dimension.

                                                              I tried "=[$(vBucket)]" but it fails and shows "-"

                                                               

                                                              Please help

                                                  • Re: Use Variable as Dimension
                                                    Vishnu Sreekumar

                                                    Helped me !