topic Re: Summation on Distinct Customer in QlikView

Summation on Distinct Customer

Tue, 15 Apr 2014 10:51:59 GMT

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

Re: Summation on Distinct Customer

anbu1984 — Tue, 15 Apr 2014 11:03:20 GMT

Can you provide example?

Re: Re: Summation on Distinct Customer

Tue, 15 Apr 2014 11:16:50 GMT

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

Re: Summation on Distinct Customer

MK_QSL — Tue, 15 Apr 2014 11:21:38 GMT

Create a straight table

Dimension

Area

Expression

1) SUM([Balance Amount])

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

Re: Re: Summation on Distinct Customer

Tue, 15 Apr 2014 11:25:55 GMT

Thanks Manish,

How does the FirstSortedValue function work?

Re: Summation on Distinct Customer

MK_QSL — Tue, 15 Apr 2014 11:29:38 GMT

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.

Re: Summation on Distinct Customer

Tue, 22 Apr 2014 13:31:53 GMT

Thank you so much Manish,

Much appreciated