2 Replies Latest reply on Jan 11, 2012 8:08 AM by marcramaer0

    Formula help needed: [Last Year Sales] + [Current Year Growth Target]

      Hello,

      Hope someone can help me. I need help with a custom calculation that computes a total of prior year sales [Sales] and adds a target sales growth for current year [Target Growth]. It will need to follow the display on month, quarter etc. Our fiscal year runs June-May, so the calculations should be limited to June 2011 onwards (Fiscal Year 2012).

      If anyone can help it would be much appreciated.

      Marc

        • 1. Re: Formula help needed: [Last Year Sales] + [Current Year Growth Target]
          Andrew Watson

          Which part are you struggling with exactly? From what's written it sounds like the 'Last Year Sales'. If this is the case you could create a calculated field along the lines of SUM(IIF([SalesDate] >= 1 June 2010 AND [SalesDate]<1 June 2011,[Sales],0)).

           

          Without seeing your data I don't know the names of your date field but this formula would sum all of the values in the [Sales] field where the sale happened between June and May - your financial year.

           

          Once the calc field is created you could then add the [Current Year Growth Target] to this in a separate calc field.

          • 2. Re: Formula help needed: [Last Year Sales] + [Current Year Growth Target]

            Still Stuck on this one, so any help is appreciated. I have figured out how to calculate prior year sales:

             

            IF [Date]<DATEADD('month',-12,#June 1, 2011#) THEN Null

            ELSEIF [Date] > DATEADD('month',-12,TODAY()) THEN Null

            ELSE [Sales]

            END

             

            Now I need to take that calculation and add it to this year's growth targets to get an absolute target value. When I sum the parts together, it totals correctly but I need the calculated field dates to display in the current fiscal year.

             

            I basically need to time shift last years sales into this year and then add the growth on top, so that for instance January 2012 Target Sales = Jan 2011 Sales + Growth Target. All I get is last year sales still displayed in last year's cell - so it needs to somehow be offset.

             

            Hope I am making sense ....

             

            Marc

             

             

             

            Any ideas?