1 Reply Latest reply: Jan 18, 2017 7:55 AM by Amirali Vastani RSS

    XIRR OnDemand

    Amirali Vastani

      Hi,

       

      I am trying to create an OnDemand version of XIRR() in chart function whereby the last entry in the Values is dictated based on user selection i.e. XIRR Time Period

       

      Currently, I have a listing of all the Outflows arranged in ascending order of date, as well as, I have appended to this table, the Inflows based on Today(), CurrentMonth, LastMonth, LastYearEnd. Basically, the inflow value are the same for each of these dates

       

      What I want is for the user to have the option to pick the XIRR Time Period field (for eg. Today, Last Month etc.. from above, only one permitted value) and then display the associated XIRR by applicable dimension.

       

      I have my Values portion returning the right information but not the dates section in my expression as there can be mulitple date values and some dates have value = 0.00

       

      Sum({<XIRRType={"Outflows"}> + <[XIRR As Of]= {"$(=Only([XIRR Time Period]))"}>} XIRRFlows)

       

      In the above expression XIRR Time Period is sitting in an island table that the user picks one value from which is then associates with XIRR As of in the Fact table. Outflows are always negative and captured first.

       

      Is it possible to get the associated dates with the XIRRFlows in the order of the XIRRFlows being read in?

       

      Here's my attempt at it which yields nulls(). I know the expression is overkill for P() notation and could be done better. This is more of an expression task than an XIRR related task.

       

      AGGR([Event Date] = P({<XIRRType={"Outflows"}> + <[XIRR As Of]= {"$(=Only([XIRR Time Period]))"}>} [Event Date]), [Entity Type], Entity, [Event Date])