Summation on Distinct Customer

[#1]

Hi All,

I have a customer database which has balances at various dates, i.e. a client can have more than one entry reflecting balances as at that date. I am developing an app that looks at total debt and then introduce various dimensions. How do I do a summation of client's balances (say area dimension) , but based on the last entry for that client, i.e. based on the last balance (by date). Lets say the fields are Customer, Date of Balance, Amount, Area.

Many Thanks

HerbieC

[anbu1984]

Can you provide example?

[#3]

Hi Anbu,

Please find attached example. I for example would need to write an expression that gives me total current Paris Balances, as well as an expression that when I click on a client it gives me their current balance.

Hope this makes sense

Thanks

H

[MK_QSL]

Create a straight table

Dimension

Area

Expression

1) SUM([Balance Amount])

2) FirstSortedValue([Balance Amount],-[Date of Balance])

[#5]

Thanks Manish,

How does the FirstSortedValue function work?

H

[MK_QSL · Accepted solution]

FirstSortedValue([Balance Amount],[Date of Balance])

This will give you Balance Amount at Min Date

FirstSortedValue([Balance Amount],-[Date of Balance])

This will give you Balance Amount at Max Date

For further details you can check the help menu or QlikView Reference Manual.

Hope this helps... please close the thread by selection appropriate answer.

[#7]

Thank you so much Manish,

Much appreciated

H