Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello,
I have a table of QlikView this:
I need to turn this table by display only the row with the maximum value of the field cost with the aggregation on the field A.
in this way:
A | B | data | max costo |
00001 | 00030 | 12/12/2014 | 1,737 |
00002 | 00001 | 12/12/2014 | 1,735 |
00002 | 00024 | 12/12/2014 | 1,735 |
00019 | 00024 | 12/12/2014 | 1,735 |
00024 | 55357 | 12/12/2014 | 1,735 |
00027 | 51443 | 12/12/2014 | 1,615 |
00030 | 00024 | 12/12/2014 | 1,735 |
00033 | 00041 | 12/12/2014 | 1,745 |
00035 | 00036 | 12/12/2014 | 1,754 |
00036 | 00040 | 12/12/2014 | 1,754 |
What kind of transformations / expressions should I enter?
Thanks
Alex
Create a Straight Table
Use
Dimension
A
and below three different expressions
FOR B
=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))
FOR data
=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))
For SUM(costo)
=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))
Hi Alessandro,
switch it to straight table and create expression max(value).
M
Hello
You can try with firstsortedvalue function.
Create a table
Dimension
A
Expression
=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))
=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))
=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))
that way I lose the information of the field b.
Could you write me an example.
Thank you very much
Alex
Create a Straight Table
Use
Dimension
A
and below three different expressions
FOR B
=FirstSortedValue(Distinct B, -Aggr(SUM(costo),A,B))
FOR data
=FirstSortedValue(Distinct data, -Aggr(SUM(costo),A,B))
For SUM(costo)
=FirstSortedValue(Distinct Aggr(SUM(costo),A,B), -Aggr(SUM(costo),A,B))