Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I'm trying to calculate 2 dynamic KPIs in a dashboard, but having a bit of trouble:
Inception Date | Rate Variation | Premium |
01/07/2020 | 50.00% | $ 2,146,427 |
01/07/2020 | 43.80% | $ 791,000 |
01/07/2020 | 18.50% | $ 3,000,000 |
01/07/2020 | 35.60% | $ 4,054,453 |
01/07/2020 | 35.00% | $ 20,975,000 |
01/07/2020 | 39.80% | $ 9,746,462 |
01/07/2020 | 35.00% | $ 10,825,000 |
01/07/2020 | 20.00% | $ 11,250,000 |
01/08/2020 | 35.00% | $ 11,993,750 |
01/08/2020 | 25.00% | $ 3,000,000 |
01/08/2020 | 35.00% | $ 8,125,000 |
01/09/2020 | 32.00% | $ 17,770,000 |
01/09/2020 | 39.00% | $ 3,400,000 |
01/09/2020 | 15.00% | $ 12,143,096 |
Can anyone guide me in the right direction?
Many thanks,
Sarah
Add a YearMonth dimension to your data model.
This is to ensure that you only have one value per month. If you can verify that you only have one [Inception Date] per month then you can skip the part where I create a YearMonth field and use Inception Date instead.
Data:
LOAD [Inception Date],[Rate Variation],Premium, Monthname([Inception Date]) as YearMonth
FROM SourceTable;
Use the following expression in Qlik Sense KPI object.
=avg({<[YearMonth] = {'$(=maxstring([YearMonth]))'}>}aggr(Rangeavg( Above( total avg( {<[Inception Date] ,YearMonth >} [Rate Variation]), 0, 3) ), [YearMonth]))
Use the following expression in a chart where you have YearMonth as a dimension.
=avg(aggr(Rangeavg( Above( total avg( {<[Inception Date] ,YearMonth >} [Rate Variation]), 0, 3) ), [YearMonth]))
To do average rate by weighted premium you just need to adjust my previous post replacing
avg( {<[Inception Date] ,YearMonth >} [Rate Variation])
with
avg( {<[Inception Date] ,YearMonth >} [Premium] * [Rate Variation])
Hi Vegar, many thanks for the suggestion. I'm getting a calculated value of 28.49% instead of 32.76% (i.e. average of all transactions over the 3 month period). Any ideas what might be happening here?
Many thanks,
Sarah