BookmarkSubscribeRSS Feed
RasmusRymark
Calcite | Level 5

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 Smiley Happy

/Rasmus

ProductIDProductCategoryActuals
1Fruits10
2Fruits20
3Fruits20
4Vegetables10
5Vegetables50

ProductCategoryForecast
Fruits40
Vegetables100
1 REPLY 1
PeterWijers
Lapis Lazuli | Level 10

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 1 reply
  • 920 views
  • 0 likes
  • 2 in conversation