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.