Qlikview: display amount according to max date

[#1]

Hi,

I need to display the balance amount from the below table (Actual Table) based on the max date in Date column.

for eg:  Refer the Actual Table below

John has 3 accounts 1st, 2nd and 3rd

in his 1st account two transactions has been done on 02-03-2016 and 23-03-2016 and showing balance of Rs. 40 and 30 resp.

so i need to display the balance amount of max date ie in his 1st account current amount is 30 and in 2nd account it should be 10 so on.

below is the expected output for clear understanding.

please help. waiting for reply.

thanks in advance.

Actual Table:

Expected Output:

Name	Account No	Date	Balance
John	1	23-3-2016	30
	2	28-3-2016	10
	3	26-3-2016	100
Daniel	6	04-3-2016	50
	7	10-3-2016	500
Josh	4	04-3-2016	200
	5	10-3-2016	20

[Kushal_Chawda · Accepted solution]

Dimension :

Name

Account Number

Expressions:

1) Date - FirstSortedValue(Date,-Date)

2) Amount - FirstSortedValue(Balance,-Date)

[sunny_talwar]

I think you will need to remove Date from your dimension and use FirstSortedValue in your expression for Balance. But I don't know what you are current expressions are so difficult to suggest anything more. Would you be able to share a sample so that we can give better suggestion

[HirisH_V7]

Hi,

Share a Sample.using  Aggr and first sorted value we can write a expression.