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.
Solved! Go to Solution.
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).
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.
No, you wouldn't want to group by date.
What might be the issue is that the date may not be in the correct order in your historical data? What I would do to make sure is to add a Sort before the Multi-Row formula (I always do that to ensure that the row-by-row comparisons are in the order I want).
See the attached workflow...
I've added the Summarize tool I mentioned earlier.
Unfortunately I cannot open your workflow my company is using 10.1.7.12 version of the software. The sort did not do much. I just wonder why it worked for the sample I have been playing with and not for the real data set.
If you open the .yxmd file in Notepad, you can change the Alteryx version to 10.0 and it should open for you.
Not sure why it wouldn't work with more data...
Great! Thank you Rodl. I have mixed up the aggregations for clients which have droped of from the portfolio with total portfolio movements but it is all good now. Thank you for your help and prompt response