16 Replies Latest reply: Dec 6, 2012 11:20 AM by katepederson RSS

    Optional Expressions?

    Brian Dunphy
      Hi,

       

       

      I have a pivot table  with 3 expressions.
      1) FCast/Actual
      2) Prior Year
      3) Budget
      However not all users need to see all 3 expressions  at all times. Sometimes they will only want to see the FCast/Actual - other  times they would like to see two expressions.

       

       

      Is it possible to  allow the user to decide what expresssions he/she wants to see?

       

       

      e.g. only show the  FCast/Actual initially and then allow then to select to see the Budget and Prior  year too if necessary

       

      If its not possible at the moment - is it something that is planned in version 11?
      Thanks
        • Optional Expressions?
          Isaiah Weed

          I believe QlikView 11 will solve for this; see "Conditional Enablement of Expressions and Dimensions" in the 11 Beta Release Notes.

           

          Alternatively you can use a macro to enable/disable expression columns.  Do some searching in the Community for this and you'll find a good few posts.  -Isaiah

          • Re: Optional Expressions?
            John Duffy

            Hello.

             

            Another option could be to create separate charts and apply a conditional expresssion in the layout tab that determines which chart is displayed.  In my attached exampe, the user can select Chart Type 1, 2 or 3 in the list box.  The selection determines which of the 3 pivot charts is displayed.

             

            John.

              • Optional Expressions?
                John Witherspoon

                You shouldn't need version 11 to conditionally enable expressions, though it depends on your chart type.  I think only conditionally enabling dimensions is new in version 11.

                 

                In a straight table, go to the presentation tab, and click on "conditional" for the column.  Enter the condition associated with the column, which will depend on how you want them to select it.  You could have a list box and look for the value being possible, or who knows what else.

              • Re: Optional Expressions?
                stefano santini

                Hi,

                u could use a variable to contain the field to use in the expression.

                I have used and it works.

                 

                the expression will be:

                 

                sum({$<   ................  >} $(variable))

                 

                Variable will contain a conditional expression to choose correct dimension decided by the user connected...

                 

                C u,

                Stefano.

                • Re: Optional Expressions?
                  Phaneendra Kunche

                  see attached for the pivot table expression selector without macros.

                   

                   

                  Thanks,

                  Phani

                    • Re: Optional Expressions?

                      Hi Phani,

                       

                      I like your solution.

                      Is there a way to use an expression like this in the script editor, when you create the inline table:

                      =Avg({$<Institution= p(Institution2) >} [Actual value])

                       

                      I tried this :

                      Metric:

                      replace LOAD recno() as MetricSequence,* INLINE [

                      Metric, Expression

                        Average, "AVG({$<Institution={'*'}>}[Actual value])"

                        "Peer Group", "Avg({$<Institution= p(Institution2) >} [Actual value])"

                      ];

                       

                      but it give an error, probably because of the symbols.

                       

                      Is it possible to have an expression like this in the inline table?

                       

                      Thanks

                        • Re: Optional Expressions?
                          Phaneendra Kunche

                          Yes you can write these type of expressions, but not in inline statements.

                           

                          1.) you can load it from Excel

                          2.) if you are not using reduce and Reload concept, you can write it in the Grid it self.

                                    a) Edit script

                                        

                          Metric table:

                          LOAD * INLINE [

                          Metric, SeqID

                          Average ,1

                          Peer Group,2

                          ];

                           

                           

                                    b.) Front end:

                          Write below this as your expression

                           

                          pick(SeqID,AVG({$<Institution={'*'}>}[Actual value]),Avg({$<Institution= p(Institution2) >} [Actual value]))

                           

                           

                                    c.) Add Metric as Dimension

                           

                           

                                    d.) Also add Metic as Selector (list box or Multi Box)

                           

                           

                          Thanks,

                          Phani

                          • Re: Optional Expressions?
                            Phaneendra Kunche

                            Yes you can write these type of expressions, but not in inline statements.

                             

                            1.) you can load it from Excel

                            2.) if you are not using reduce and Reload concept, you can write it in the Grid it self.

                                      a) Edit script

                                          

                            Metric table:

                            LOAD * INLINE [

                            Metric, SeqID

                            Average ,1

                            Peer Group,2

                            ];

                             

                             

                                      b.) Front end:

                            Write below this as your expression

                             

                            pick(SeqID,AVG({$<Institution={'*'}>}[Actual value]),Avg({$<Institution= p(Institution2) >} [Actual value]))

                             

                             

                                      c.) Add Metric as Dimension

                             

                             

                                      d.) Also add Metic as Selector (list box or Multi Box)

                             

                             

                            Thanks,

                            Phani

                        • Re: Optional Expressions?
                          Brian Dunphy

                          Thanks Guys. Why cant we just have the option of a small dropdown in the caption box to open up a list of checkboxes to enable/disable expressions?