Hi all
I'm trying to create a report containing sales and forecast (and index calculation based on these). My goal is a report with a product-hierarchy with the actual sales in one column - and the forecast in the nex column. My issue is that the forecast isn't made on the each individual product - but only on product Groups. My input data is illustrated below.
Does anyone have an idea on how I can merge these into one report? Since VA doesn't allow join 2 datasets directly in the report (as far as I know) then I think I need to join the tables on ProductCategory (obviously). But how can I show the FC only on the ProductCategory when drilling in the AC's in the report? When I join the below tables I get a FC of 40 on both Product 1+2+3 = 120. I could maybe just create a Measure as average - but the FC will still be 40 on each product. But 40 on top of the category.
I hope someone can help. AND understand my issue
/Rasmus
ProductID | ProductCategory | Actuals |
---|---|---|
1 | Fruits | 10 |
2 | Fruits | 20 |
3 | Fruits | 20 |
4 | Vegetables | 10 |
5 | Vegetables | 50 |
ProductCategory | Forecast |
---|---|
Fruits | 40 |
Vegetables | 100 |
Hi Rasmus,
I solved this issue once appending the data tables.
set the forecast value to 0 for the actuals and set prodID to blank/zero.
set the actual value to 0 for the forecast.
append the data allowing you to report both situations and combined on category level.
but all of this outside of va.
greetings Peter
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.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.