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
- Calculate the average call volume using the total call volume divided by the number of months.
- Verify the index values by dividing each month’s call volume by the average call volume.
- 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)