Help with XIRR

Mon, 01 Jun 2015 22:00:46 GMT

Hello

QlikView has a formula to calculate the XIRR.

The formula basically calculates payments cashflow based on periods and returns internal rate.

In a basic example:

xirr( Payments, Dates )

But I need to use an aggregation of payments cashflow, like the example:

xirr( total <Grp> Sum(total <Grp> Payments), Dates)

Then Help QlikView says:

Valueexpression and dateexpression must not contain aggregation functions, unless these inner aggregations contain the total qualifier. For more advanced nested aggregations, please use the Advanced Aggregation function in combination with calculated dimensions, see Add calculated dimension... .

If the word total occurs before the function arguments the calculation will be made over all possible values given the current selections but disregarding the chart dimension variables.

The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimension variables. In this case the calculation will be made disregarding all chart dimension variables except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields. Also fields which are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the variables in the group causes the function to work when the cycle or drill-down level changes.

It makes perfect sense, but in practice doesn't works.

Has anyone used this formula using aggregation?

Tks!

Re: Help with XIRR

ramoncova06 — Mon, 01 Jun 2015 23:38:56 GMT

I haven't used that formula, but you need to use an aggr for your sum to work properly

QlikView Technical Brief - AGGR.docx

Re: Help with XIRR

robert_mika — Tue, 02 Jun 2015 05:00:33 GMT

Post your expression/app and desired output...

Re: Help with XIRR

Wed, 03 Jun 2015 14:14:11 GMT

I found solution!

For calculate aggregations in XIRR i needed a final aggregation with Sum and Total. I never needed do this aggregation level, but this works.

XIRR(

//Payments

if([nPer]=0, (Sum(Total Aggr(Sum(Total <Group> [Metric]),Group) ) * -1) ,

(Sum(Total Aggr(Sum(Total <Group> [Metric]),Group) ) / PmtFactor)

)

//Dates

if([nPer]<=72 , [Date])

)

Thanks!

topic Re: Help with XIRR in QlikView

Help with XIRR

Re: Help with XIRR

Re: Help with XIRR

Re: Help with XIRR