5 Replies Latest reply on Mar 7, 2013 11:13 AM by Brian Smith

# Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

History:

I have calculated date  "Forecasting Date" :  If [Units]>=0.001 Then [Start Date ] else [End Date] end. Start Date and End Date are Date Parameter from 2012-2020.

I have several additional Parameters called "2012 What if % x, 2013 What if % x, 2014 What if % x" .....and so on for each year so that I can vary the forecasted trends in each individual year. these parameters are integer 100-300 step size is 1.

I have several calculated measures(Year Units) which build on each other starting with using 2012 as baseline data, "2012 Units, 2013 Units, 2014 Units" etc etc

[2012 Units] = [Total Forecasting Units Base]*[2012 What if % x]/100

[2013 Units] = [2012 Units]*[2013 What if % x]/100

[2014 Units] = [2013 Units]*[2014 What if % x ]/100    ......and so forth you get the picture.

I then combined each individual Year Units measure in one measure like this:     called[Forecast Units]

If (DATEPART("year",#1/1/2012#)= 2012) and not ISNULL([2012 Units]) then [2012 Units]

elseif (DATEPART("year",#1/1/2013#)= 2013) and not ISNULL([2013 Units]) then [2013 Units]

elseif (DATEPART("year",#1/1/2014#)= 2014) and not ISNULL([2014 Units]) then [2014 Units]

elseif (DATEPART("year",#1/1/2015#)= 2015) and not ISNULL([2015 Units]) then [2015 Units] ..............etc. etc.  ........

My problem is this: If I stack up each individual Year Units in rows on measure values shef and place my Forecasting Date" as Column The measure values all populate under Forecast Date 2012, however the values are right and the parameter controls work perfectly on each one building on the next...

If I use my combined measure it solves the problem and places the values out across in the appropriate column of Forecast year, however the values are incorrect and only the 2012 and 2013 What if % x parameters work.

I have tried this back and forth with both the combined and individual measures trying to figure out if I could also make the "Year Units" more Forecast Date specific in an IF statement such as happens in the combined [Forecast units], which doesnt work and also tried different variations of the date specifics for the [Forecast units] which always results in the "cannot mix aggregate and non aggregate" error. I tried using MIN/MAX but that doesn't work b/c I have multiple years. I tried referencing the date range inside the Start Date & End Date Parameters and that didn't work.........

What perplexes me is that one half of it works in one view and the other in the other but not both together as they should be. I can either have correct data and incorrect date column or visa versa..... I have toyed with this long enough and surfed around wasting a day trying to solve this. Please Help if anyone has run into this or knows what I am talking about.

I cannot post a sample workbook b/c data is extremely confidential and i have to run it through compliance which will take until next year to get through or i would have posted one sorry.

• ###### 1. Re: Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

Brian,

Here is a packaged workbook that does something similar to what I think you are describing.  It uses a calculation to push the last date into the future and then, using domain padding, fills in the missing value with the actual value while replacing the last date value with the forecast value.  If that sounds confusing, it is!

And this is just a silly example I came up with a while ago because the question came up in an internal training event.  So the solution is not perfect and breaks down fairly quickly in this example.  However, it might get you going.

I'd be happy to answer any questions you might have!

Regards,

Joshua

• ###### 2. Re: Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

I'll take a look. Thanks Josh. Yes kind of confusing i agree. That is exactly how I did it using domain padding and then building on the previous value. Originally I did this with 1 Parameter Multiplier to adjust the % trends which worked great at charting a 10- 20 yr avg. ann trend.... But of course then once it was being used in presentations someone said.."well cant you build us something so we can adjust the trend of each year individually" ..... which is how I got to the point I am stuck at now.....

Again Thanks!

• ###### 3. Re: Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

Joshua, Do you care if I recreate what I have using your training sample wkbk so I can have a better demonstration and then attach that in here?

• ###### 4. Re: Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

Brian,

Absolutely!  Use it however you want and if you want to post a derivative here that would help the discussion, that'd be great!

• ###### 5. Re: Really not sure of the correct title for this ?......(Forecasting, Parameters Aggregate v. Non Aggregate etc. )

Joshua,

Attached is the copy. After playing with it a while I couldn't recreate the problem exactly but pretty close.

On #1 you can see where the measures I created are working w/ parameters but not the projected date

and then on #2 and #3 you can see the error message I get with using two variations trying to project out

each individual value across the dates. Thanks for your help!

then 2 ways i tried are "Brians FORECAST SALES" and "B-Actual & Projected Sales". I triend multiple different combinations within these 2 and could never get anything to work correctly.

Thanks again.

Brian