Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Aggr Function gives unexpected results

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.

Tags (1)
1 Reply

Re: Aggr Function gives unexpected results

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

talk is cheap, supply exceeds demand