1 Reply Latest reply: Jan 24, 2016 1:13 PM by Gysbert Wassenaar RSS

    Aggr Function gives unexpected results

    Daniel Wiener

      Hi, I'm struggling with the Aggr function. What I'm trying to achieve is the following which relates to returns on securities. I have a range of securities each with monthly returns, so my fields are Date, Security and SecReturn. To aggregate returns I need to arithmetically sum returns across securities for each month and then geometrically sum those returns across multiple months. I've created a Master Measure called Contribution which equals Exp(Sum(Log(Aggr(Sum(SecReturn), Date)+1)))-1

       

      This works fine when looking at the overall result (i.e. all securities), but when looking at it in a chart where security is a dimension it doesn't work as it seems to randomly apply the monthly return to one security each month. I need it to work for both. Note, I don't want to create two dimensions since I have groups of securities and this function needs to work across those groups too.

       

      Any help would be appreciated.