Calculating Weekly WAPE without using week dimension in the visual

likhithl · July 23, 2024, 6:00am

Hello QuickSight Community,

I’m working on a WAPE (Weighted Absolute Percentage Error) dashboard and encountering an issue with the total WAPE calculation. Here’s the situation:

Current Setup:

I have weekly data for actuals and forecasts.
WAPE formula: abs(sum(actual) - sum(forecast)) / sum(actual)
This formula works correctly at the weekly level.

The Problem: When calculating the total WAPE, it’s not summing the weekly absolute differences. Instead, it’s taking the absolute difference of the totals. Here’s an example:

Week	Actual	Forecast	Abs Difference
2024-1	952.51	904.18	48.33
2024-2	943.93	915.63	28.29
2024-3	966.53	983.33	16.80
Total	2862.96	2803.14
Sum of Abs Differences	93.43
Abs Difference of Totals	59.82

I need the 93.43 value for the correct WAPE calculation, but I’m getting 59.82.

Question:
How can I modify my formula or approach in QuickSight to ensure it sums the weekly absolute differences rather than taking the absolute of the difference of sums?

Any insights or solutions would be greatly appreciated. Thank you in advance for your help!

prantika_sinha · July 23, 2024, 8:45am

Hi @likhithl ,

Welcome to the QuickSight Community!

Since the aggregation is not partitioned by week, the computation aggregates the number at the overall level. Let break down this formula for implementation.

Step 1 : Weekly Error margin = abs(sumOver(actual,[week])-sumOver(forecast,[week]))
Step 2 : Overall WAPE = Sum(weekly error margin)/sum(actual)

You may need to tweak the formula if your data granularity is not weekly. But the approach would be similar - calculate weekly error margin and then aggregate numerators and denominators to fetch the overall percent error.

If this does not work, would suggest you to kindly share a sample data in the Arena.

Thanks,
Prantika

Xclipse · July 30, 2024, 3:52am

Hi @likhithl, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!