4 Replies Latest reply: Mar 8, 2013 7:01 AM by Prem Kumar Thangallapally RSS

    How to get min value in a chart expression?

    Leandro Duarte

      Dear Colleagues,

       

      I'm trying to get in a pivot table a minimum value results of an expression. Basically from the table below I want to get the minimum value of column "Average", which means in this case the column "Min Value" should be 110.

       

      I have tried first, min, bottom, lower. The bottom is taking the result of the sort order.

       

      Please note, behind the column "Average" I have one expression.

       

      Reference AverageMin Value
      A155110
      C123110
      D110110
      E119110

       

      I really appreciate a support for this case.

       

      Please find enclosed the example.

       

      thanks in advance,

      Leandro Duarte

       

        • Help on Chart Expression - min value

          min( all aggr(avg(Result),Reference))

            • How to get min value in a chart expression?
              Leandro Duarte

              thank you Marc, I have one additional question, if I want to have another column and them summarize by them, what would be the right expression?

               

              example below:

               

              QuarterProductGroupAverageMin value
              1bbb8989
              axxx11089
              cabcd12089
              2cabcd9494
              axxx115115
              bbb125125
              3axxx9999
              bbb12099
              cabcd13099
                • How to get min value in a chart expression?
                  Stefan Wühl

                  Use the TOTAL qualifier with field list instead of ALL:

                   

                  =min( total<Quarter> aggr(avg(Result),Quarter,Product))

                   

                  And please read this from the Help:

                   

                  If the word total occurs before an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions.

                   

                  The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields. Also fields which are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the dimensions in the group causes the function to work when the cycle or drill-down level changes.


                  In previous QlikView versions, the all qualifier may occur before an expression. This is equivalent to using {1} total. In such a case the calculation will be made over all the values of the field in the document, disregarding the chart dimensions and current selections. (The same value is always returned regardless of the logical state in the document.) If the all qualifier is used, a set expression cannot be used, since the all qualifier defines a set by itself. For legacy reasons, the all qualifier will still work in this QlikView version, but may be removed in coming versions.

                   

                  So I would recommend avoiding ALL anyway.

                   

                  Hope this helps,

                  Stefan

                  • Re: How to get min value in a chart expression?
                    Prem Kumar Thangallapally

                    Add column and select any field value in that new coumn and drag to upside of that table
                    while you dragging the qlikview will automatically give indication by giving blue colour arrow
                    make sure that when you dragging that field value you must be get left arrow on top of the pivot table

                     

                    then it will show your following requirement

                     

                     

                     

                    -regards
                    Premhas