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

# How to get min value in a chart expression?

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 Average Min Value A 155 110 C 123 110 D 110 110 E 119 110

I really appreciate a support for this case.

Leandro Duarte

• ###### Help on Chart Expression - min value

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

• ###### How to get min value in a chart expression?

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:

 Quarter Product Group Average Min value 1 b bb 89 89 a xxx 110 89 c abcd 120 89 2 c abcd 94 94 a xxx 115 115 b bb 125 125 3 a xxx 99 99 b bb 120 99 c abcd 130 99
• ###### How to get min value in a chart expression?

Use the TOTAL qualifier with field list instead of ALL:

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

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?

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