I am trying to do a calculation of forecast performance in visual analytics.
The answer I need is a nuanced version of the mean absolute percentage error but hopefully this example will explain.
In this spreadsheet i have some random data by date and some other groupings with forecasts and what the outturn value was, and 3 different ways of calculating the absolute percentage error. option 1 I can do in VA by doing a sum by group abs(forecast-outturn) but you can see that because I have different sectors etc the percentage is way too high. option 2 I can do by doing abs(sum by group forecast - sum by group outturn) but this is way too low because the errors are netting out between the different days.
option 3 is what I need but I don't know how to get VA to do the sum by date first and then work out the total as a second step.
I know that one option would be to use aggregated tables to get the totals by date and then calculate from there but for the final report we are interested how it differs if I filter on just a specific sector or class so I need it to be more flexible.
If anyone has any insight on this it would be much appreciated.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.