Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
kebbells
Contributor
Contributor

Average Rate over last 3 months

Hi I'm trying to calculate 2 dynamic KPIs in a dashboard, but having a bit of trouble:

  1.  Average Rate Variation (of transactions) over the last 3 months (there may be 2 years worth of data in the dataset)
  2. Average rate by weighted premium (Rate Variation*Premium)/Total Premium (of the last 3 months in the dataset)

 

Inception DateRate VariationPremium
01/07/202050.00% $   2,146,427
01/07/202043.80% $       791,000
01/07/202018.50% $   3,000,000
01/07/202035.60% $   4,054,453
01/07/202035.00% $ 20,975,000
01/07/202039.80% $   9,746,462
01/07/202035.00% $ 10,825,000
01/07/202020.00% $ 11,250,000
01/08/202035.00% $ 11,993,750
01/08/202025.00% $   3,000,000
01/08/202035.00% $   8,125,000
01/09/202032.00% $ 17,770,000
01/09/202039.00% $   3,400,000
01/09/202015.00% $ 12,143,096

 

Can anyone guide  me in the right direction? 

Many thanks,

Sarah

3 Replies
Vegar
MVP
MVP

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]))

 

 

Vegar
MVP
MVP

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])

kebbells
Contributor
Contributor
Author

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