1 Reply Latest reply on Aug 9, 2017 3:43 PM by Patrick A Van Der Hyde

    Forecasting new fiscal year data based off of prior fiscal year

    Bjorn Johnson

      So I have a dashboard showing FY17 actuals and FY18 actuals (we're one month into FY18 fiscal year so there's not much data). The goal of the dashboard is to compare FY17 actuals, FY18 actuals, and FY18 expected. My question here is about how to add FY18 expected data. One graph I have is a line graph where the X axis is month and the Y axis is conversions. I want to be able to apply our FY17 conversion rates for each date interval (weekly in my instance) to our FY18 campaigns' mail quantities.

       

      To go a little more in depth, I am dealing with direct mail here where I have a lot of different direct mail campaigns dropping at different days where 100% of the mail quantity is dropped on one day for each campaign; e.g. FY17 Campaign1 has a mail drop date of 9/15 with a mail quantity of 10,000 on 9/15, and had 10 conversions after the first week, 25 conversions after the second week, 50 conversions after the third week, and so on. The fields for Mail Drop Date and Conversion Date are different fields in the data source (I think I've gotten a start on getting these conversion rates like so: SUM( [Conversions] )/ SUM( { FIXED [Campaign Name] : SUM([Mail Quantity])} ) This calculation gets the correct conversion rates for each campaign for the most part.

       

      How can I apply a calculation similar to this on my FY18 actuals? Alternatively is there a way I can apply a trend line I have for FY17 to FY18? e.g. [FY17 Trend Line] * [FY18 Campaign1 Mail quantity]?