Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please find enclosed the example.
thanks in advance,
Leandro Duarte
min( all aggr(avg(Result),Reference))
min( all aggr(avg(Result),Reference))
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 |
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
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
one Column with multiple value values to find least values in qliksense. What kind of function to use to get min value.