Say we run 5 different strategies for small organizations. Clients want us to track account gains (openings) and losses (closings) every quarter. For example, x strategy lost 5 accounts and y strategy gained 4 accounts. They use the overall firm's gains and losses as a benchmark to compare against the strategies they employ. For example, overall we lost 3 accounts and gained 5 during the quarter. Their strategy category gained 5 accounts, lost none. But also need assets (AUM) for the overall firm and then for each of the strategies. I got that but the logic challenge here is that they also want assets for what was gained and lost based on the following logic: say if Q1, then want any losses by strategy in January to use the 12 31 AUM (1/31 close AUM amount won't be there). If a certain strategy account closed (Loss) in Feb then use Jan 31 AUM. However, if a new account opens in March, then use 3/ 31 AUM. If new account opened in Feb then use Feb 28 AUM, etc.
I can do this using a Pivot but want to stack the data and use countifs, sumifs. Would truly appreciate logic assistance for yellow. Thanks
I can do this using a Pivot but want to stack the data and use countifs, sumifs. Would truly appreciate logic assistance for yellow. Thanks
