Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculate Balance Movements at the Client Level and Rank Clients

KB
7 - Meteor

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. 

6 REPLIES 6
RodL
Alteryx Alumni (Retired)

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).

KB
7 - Meteor

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.

 

 

RodL
Alteryx Alumni (Retired)

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.

KB
7 - Meteor

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.

RodL
Alteryx Alumni (Retired)

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

KB
7 - Meteor

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

Labels