Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to get a specific value on expression

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?

7 Replies
goldnejea8
Partner - Creator
Partner - Creator

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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