Aggr Function gives unexpected results

Wed, 20 Jan 2016 10:12:34 GMT

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.

Re: Aggr Function gives unexpected results

Gysbert_Wassenaar — Sun, 24 Jan 2016 18:13:53 GMT

Perhaps this works: Exp(Sum(Log(Aggr(NODISTINCT Sum(SecReturn), Date)+1)))-1

topic Re: Aggr Function gives unexpected results in App Development

Aggr Function gives unexpected results

Re: Aggr Function gives unexpected results