01-26-2015 07:53 AM
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
01-27-2015 01:23 PM
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.