Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Summation on Distinct Customer

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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.

View solution in original post

6 Replies
anbu1984
Master III
Master III

Can you provide example?

Not applicable
Author

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
MVP
MVP

Create a straight table


Dimension

Area

Expression

1) SUM([Balance Amount])

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

Not applicable
Author

Thanks Manish,

How does the FirstSortedValue function work?

H

MK_QSL
MVP
MVP

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.

View solution in original post

Not applicable
Author

Thank you so much Manish,

Much appreciated

H