Calculate Balance Movements at the Client Level and Rank Clients

I am looking to calculate balance movements at the client level (thousand clients at any given point of time/date). I have tried to use multi-row formula but it seems to work at the portfolio level only. For example when I aggreagate/sum numbers to two rows for two points of time (31/03/2016 and 30/04/2016) the sum bewteen these two balance is calculated correctly but when I try to do it at the client level it does not work. Once this is calculated I would like to rank clients in order to be able to identify top clients with positive movements and top clients with negative movements between any given two periods of time. Any help will be appreciated.

Workflow

Tips and Tricks

Common Use Cases

Accepted answers

Thank you Rodl. That is what I want. So at the end you can compare how the client balance has change between March and April for example. It seems to be working on the sample data set I have created but not so much on the real data I have. Maybe there is some data issue? Should I check the date field too since there is a historical data. The attached is the successful sample calculation.

Client_test.xlsx

Multirow.yxmd

All comments

RodL

Not sure I'm understanding your scenario entirely, but if you want to start your row-by-row comparison for each client, you want to check the client ID field in the Group By section of the Multi-Row Formula tool. That will keep the calculations to within a specific client.

Then you would probably want to add a Summarize tool grouping by client and set to calculate the Max and Min values (of the differences) for each client.

You could then use a Sort tool to set the ranking up (with a Sample tool to give you the "top N" of your clients).

Client_test.xlsx

Multirow.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703