While calculating the returns on financial assets, we will often look at the returns from multiple holding periods. For example, one may hold an asset for five years, and the asset may have earned total 150% returns over this period. However, it is difficult to interpret these returns as we cannot compare them with returns on other assets or benchmarks. For comparison purpose, we will have to aggregate these returns for the same period such as daily returns, monthly returns, or yearly returns.
While calculating the aggregate returns, our return measure will vary depending on what method we use to calculate the aggregate returns. Two common methods are arithmetic returns and geometric returns. Let’s take an example to understand both these methods. Let’s say that our portfolio generated the following returns in 5 months:
Arithmetic method = (5,23% + 4,37% +1,66% + 4,77% -6,76%) = 9,25%
Geometric method = (1+0,0523) * (1+0,0437) * (1+0,0166) * (1+0,0477) * (1-0,0676)-1 = 9,05%
Two questions:
1. Is Qlik Sense able to create, next to the SUM formula, also a formula based on the geometric method?
2. If the above questions is no, is it possible to solve the problem with Qlik Sense Set Analysis or by writing a script?
