I feel your pain -- you may have seen my post Order of IRR payments. I have not obtained correct results with IRR unless the data was loaded in order. Maintaining order is impossible if any amount repeats.
Last time I checked, the sorting in Aggr() only applies to the data fed to the Aggr expression parameter. It does not make Aggr return sorted results. Disappointing.
You might try XIRR as a workaround. I've gotten close, but never exactly duplicated IRR results with XIRR.
Thank you for responding to my post. Yes, I did read your post, and I reread it again to get some additional information. I did manage to get the XIRR function to work on the total of all accounts. The formula I used was:
xirr(aggr(sum(CashFlow), MthNum), MakeDate(2017,2,1) + (MthNum*365.25/12))
This gets me pretty close to the IRR computation (it will change depending on the start date; the above formula uses 2/1/17 as the start date).
Then I tried using the following formula to compute an IRR by state:
xirr(aggr(sum(CashFlow), State, MthNum), MakeDate(2017,2,1) + (MthNum*365.25/12))
I would assume that this would summarize the cash flows by state and month, and then compute an IRR for each state. I then tried creating a simple table with state and this measure (the XIRR formula). I get a spinning wheel in the chart for several minutes, and then a "Calculation timed out" error. I am working with a reasonably large data set (just over 1 million records) and am working on a desktop (new machine with an i7 processor and 32 Gb of ram). So either my XIRR formula is done incorrectly, or Qlik Sense is just not the best tool to handle this kind of computation.
I am going to mark this post as "Answered", as I think your response has taken me as far as I can get, but if you do have any other ideas that you think might be worth trying, please let me know.
Thanks again for responding!