Max value in Set analyse ...

[jozisvk11]

Sum({< [Account] = {'326100'}, [Type] = {'5'} , Max(Rank of the row)>}Round([Sum of money],0.01))), 0.01)

At first, I have table :

ID             Rank of the row            Type                Sum of money

K01                 1                              5                         100

K01                 2                              5                         200

K01                 3                              5                         700

K02                 1                              5                         300

K03                 1                              5                         500

K04                 1                              5                         550

K04                 2                              5                         650

...

I need filter according to Max(Rank of the row) But max function don´t run.

My result should be :

ID             Rank of the row            Type                Sum of money

K01                 3                              5                         700

K02                 1                              5                         300

K03                 1                              5                         500

K04                 2                              5                         650

Thanks for helping.

[swuehl]

Rank of the row is a field?

Try

FirstSortedValue({< [Account] = {'326100'}, [Type] = {'5'} >} Round([Sum of money],0.01), -[Rank of the row])

[Kushal_Chawda]

Sum({< [Account] = {'326100'}, [Type] = {'5'} ,[Rank of the row] ={"=aggr(max([Rank of the row]),ID)"}>}Round([Sum of money],0.01))

[sunny_talwar]

Do you think this will work? I have a feeling that it might not.

May be this:

Sum({< [Account] = {'326100'}, [Type] = {'5'}, ID ={"=[Rank of the row] = Aggr(Max(TOTAL <ID> [Rank of the row]), ID, [Rank of the row])"}>} Round([Sum of money],0.01))

[Kushal_Chawda]

I had same feeling but somewhere I had seen this kind of expression,so let's try

[sunny_talwar]

I guess the best way would be to test it out. I will be back with my findings based on this sample at least

[swuehl]

to add to my previous answer, you need to use a table with dimensions ID and Type

and two expressions:

=Max({< [Account] = {'326100'}, [Type] = {'5'} >}  [Rank of the row])

=FirstSortedValue({< [Account] = {'326100'}, [Type] = {'5'} >} Round([Sum of money],0.01), -[Rank of the row])

[Kushal_Chawda]

Yes. I don't have QV to test.. adha kaam mera hava me hota he

[nizamsha]

not the best way but u can use this it will give the op what u want

Dimension: =Aggr(MAx([Rank of the row]),ID) and also add the remaining dimension column

expression : =MAx(Aggr(max([Sum of money]),ID))

[sunny_talwar]

Both the methods are not working. Although one way would be to create a flag in the script

Table:

LOAD * Inline [

ID, Rank of the row, Type, Sum of money

K01, 1, 5, 100

K01, 2, 5, 200

K01, 3, 5, 700

K02, 1, 5, 300

K03, 1, 5, 500

K04, 1, 5, 550

K04, 2, 5, 650

];

Join (Table)

LOAD ID,

  Max([Rank of the row]) as [Rank of the row],

  1 as Flag

Resident Table

Group By ID;

And then using the flag:

=Sum({<Flag = {1}>}[Sum of money])

Using Flag is easy and efficient, but makes the expression Static, So not sure if you would want that.

Another Alternative would be to create a Key for ID and Rank of the row field.

Table:

LOAD *,

  AutoNumber(ID&[Rank of the row]) as Key;

LOAD * Inline [

ID, Rank of the row, Type, Sum of money

K01, 1, 5, 100

K01, 2, 5, 200

K01, 3, 5, 700

K02, 1, 5, 300

K03, 1, 5, 500

K04, 1, 5, 550

K04, 2, 5, 650

];

And then my initial idea will work:

Sum({<Key ={"=[Rank of the row] = Aggr(Max(TOTAL <ID> [Rank of the row]), ID, [Rank of the row])"}>} Round([Sum of money],0.01))

But what is the point of doing all this, if Stefan's expression using FirstSortedValue() is doing exactly what might be needed

Attaching the qvw with these options attached.

Best,

Sunny