Average calculation

Question & Answer
, , ,
1

I’m new to this group and I’m hoping to receive some assistance with a matter I’m currently handling. I’m working on forecasting and I’m seeking clarification on how the tool calculates index and average values. My data is derived from historical records, and I’ve shared below the monthly call volumes along with the corresponding index values that I’ve obtained:

janvier février mars avril mai juin juillet août septembre octobre novembre décembre
calls 1037875 897302 956771 832187 789365 845788 815744 906206 888391 869919 860202 821555
index 1,16 1,11 1,07 0,96 0,88 0,98 0,91 1,01 1,03 0,97 0,99 0,92
2

Hello @amineaoula , welcome to the QuickSight community!

Could you provide more context for how you are using the average? Did you create a calculated field using the avg function?

3

Thank you Duncan for you response,

I collect historical data, which is the number of calls we receive in a year. Our tool then predicts future call volume by calculating the average, something I can’t do on my own, hence why I need assistance. The index represents the ratio of historical calls to the average. However, the average changes each month. If I simply divide the total calls by 12 (the number of months), I notice a slight difference. My main concern is understanding the method so I can confidently present the results.
when i searched the documentation of tool all i found was that : The index values represent how a time period compares to the average.

thank you for your help

4

Hi @amineaoula, to assist you with forecasting and understanding how index and average values are calculated in Amazon QuickSight, let’s break down the process step-by-step.

Understanding Index Values

The index value is a ratio that compares the call volume for a given month to the average call volume across all months. The formula to calculate the index for a particular month is:

Index for month=Call volume for month/Average call volume

Given your data:

  • Calls (monthly): The number of calls received each month.
  • Index: The ratio of calls for each month to the average call volume.

Calculating the Average Call Volume

To calculate the average call volume, sum up the total number of calls for the year and divide by 12 (the number of months).

Average call volume=∑(Monthly calls/12)

Using your data:

Total calls Total calls=1037875+897302+956771+832187+789365+845788+815744+906206+888391+869919+860202+821555

Let’s calculate this:

Total calls=10504304

Average call volume=10504304/12≈875358.67

Verifying the Index Values

Now, we can verify the index values provided by using the calculated average call volume:

For example, for January:

Index for January=(1037875/875358.67)≈1.16

We can repeat this calculation for each month to ensure the index values match the provided data.

Forecasting Future Call Volumes

To forecast future call volumes, you can use the index values. The index values represent how each month compares to the average, so you can predict future call volumes by multiplying the expected average call volume by the index value for each month.

Example Calculation

If the predicted average call volume for next year remains the same (875358.67), then:

  • Predicted calls for January = 875358.67×1.16≈1019390
  • Predicted calls for February = 875358.67×1.11≈971648

And so on for each month.

Summary

  1. Calculate the average call volume using the total call volume divided by the number of months.
  2. Verify the index values by dividing each month’s call volume by the average call volume.
  3. Forecast future call volumes by multiplying the predicted average call volume by the index values.

I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)