how to get a specific value on expression

[#1]

HI

I have a chart with balance by rowid for specific client

ie.

client    transid   balance     rowid

00          111             0          1

00          212             5          2

00          301             8          3

00          432             5          4

00          523             0          5

01          645             5          6

01          767            10         7

01          843            15        8

01          934            20        9

01          102            15        10

01          115            18        11

02          128            20        12

02          130             5          13

I used a function aggr in expression to get last rowid for a specific client but i need to get balance for this rowid

aggr( max(ROWID), Client))

This function return for client 00 the rowid= 5, i need balance = 0

How i can get this?

[goldnejea8]

Try using aggr( min(ROWID), Client)) to get the first balance. 

Would you always need the first balance, or would it change depending on the scenario?

[Gysbert_Wassenaar]

You can use the firstsortedvalue function. Create a straight table, add client as dimension and two expressions:

     Rowid: FirstSortedValue(rowid, -rowid)

     Balance: FirstSortedValue(balance, -rowid)

See attached example

[#4]

ok, balance is a acumulated field, the last rowid is the actual balance to the client. In the table i have dates, the data is sort by date.

Depending of the date selected i need get the last rowid to the client.

[#5]

i used the firstsortedvalue fuction, but in somes cliente appears "-" null, i don´t know why?

[#6]

ok.

the first two tables have problems with showing the data.

the next table have the correct values but only field saldo.

the last table is that you suggest made.

sorry for my english, i speak spanish.

[Gysbert_Wassenaar]

In the cases where firstsortedvalue returns a null, more than one value of balance_ref was returned. Check transid 91442. In a table box I get 4 records back for two cardcodes and two amounts each. It's not possible to determine which value is the one that should be used.

[#8]

ok, thanks... I change the relations addin cardcode to table CxC_Balance_Ref. The difference now is too short.

i m newbie, how you can view the function return this values? please explain. Just i have 2 weeks with qlikview