Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Can you provide example?
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
Create a straight table
Dimension
Area
Expression
1) SUM([Balance Amount])
2) FirstSortedValue([Balance Amount],-[Date of Balance])
Thanks Manish,
How does the FirstSortedValue function work?
H
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.
Thank you so much Manish,
Much appreciated
H