1 Reply Latest reply: Jan 17, 2014 1:46 PM by Nicole Smith RSS

    Average continue to future months

      Hi all

       

      My first post here. I have used this community with great succes - so thanks to all

      However, I have not had succes in finding the answer for my current problem, so I'm hoping for your help.

       

       

      Basically I'm trying to calculate previous months average, and using them to calculate a forecast. So in the past I have both 'number of units sold' and 'sales price'. For future months I only have the forecast for 'expected units sold'.

       

      For everything in the past (and present) I have no problems. I use the following set analysis to calculate the average of a unit sa:

       

      sum( aggr( rangesum( above( sum({<BIAccount_Name={'Unit Price'}>}M_FinancePerformanceAmount),0,6) ),CalendarPrimary_MonthId, UnitType))

      /

      sum( aggr( rangesum( above( count({<BIAccount_Name={'Unit Price'}>}M_FinancePerformanceAmount),0,6) ),CalendarPrimary_MonthId, UnitType))

       

      What I want is to forecast the turnover, by simply multiplying my forecast column (which is nicely filled in), with the calculated average. Problem is that the average column, is only filled in, in past and present months - not in future. So I can't multiply the two columns

       

      To put it another way. My set analysis above, doesn't work in the future. What can I do??

       

      Thanks all!