topic Re: Can data be aggregated within the IRR function? in App Development

Can data be aggregated within the IRR function?

Mon, 20 Feb 2017 22:54:14 GMT

I want to compute IRRs at various grouping layers and wondering if this is possible within Qlik Sense at the chart level.

Each account in our data contains a month number (-1 to 157) and a cash flow amount. Thus account #1 has 159 rows (MthNums -1 to 157), account #2 has 159 rows, and so on. I want to aggregate the cash flow amounts up to various dimensions like state, or type of account, or just a grand total across all dimensions where all accounts are summed up together.

I have tried the following formula for the grand total IRR:

IRR(aggr(sum(CashFlow), MthNum))

with no result.

Then I read about sort order and tried the following:

IRR(aggr(sum(CashFlow), (MthNum,(NUMERIC,ASCENDING)))).

In my mind, the aggr function should produce a temporary table of CashFlow numbers that are totalled for each MthNum -- thus the exact array of values that would be needed to compute an IRR. In fact I have created a temporary chart with MthNum and sum(CashFlow), downloaded the values into excel and run an IRR function across the cash flow numbers with a valid answer. However, in Qlik Sense, this also returns no result.

I had read in the forum that there might have been some issues with the IRR function, but I thought that sorting in the aggr function was supposed to solve the issues. The help document talks about using aggr within the IRR function, but doesn't give any examples.

Can anyone offer some assistance?

Re: Can data be aggregated within the IRR function?

rwunderlich — Mon, 20 Feb 2017 23:51:33 GMT

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.

Re: Can data be aggregated within the IRR function?

Wed, 22 Feb 2017 14:24:43 GMT

Rob,

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!

Re: Can data be aggregated within the IRR function?

Wed, 22 Feb 2017 14:32:33 GMT

Sorry, I wasn't able to figure out how to mark this as answered, so I marked it Helpful instead.