1 Reply Latest reply on Mar 15, 2016 2:50 PM by diego.medrano

    Creating 3 calculations (Some affected by filters others not) to create a client status. Comparing / Calculating different averages by periods

    athena.nader

      Hi All!

       

      Thanks in advance for your feedback! I'm hoping someone could help me on how to do this.

       

      I need to create a client status, base on 3 different calculations and 2 filters one being a month and the other percentage of when the client gets flag.

       

      If the client is positive on any of these 3 calculations then they are flag as Growing or Decreasing. (I've attatched a work book but non of my calculations are valid)

       

      I'll make the equations I need in excel format so you can understand what I need:

      1. Customer Status ( IF( OR(((( Formula 1 <= Parameter %), (Formula 2 <= Parameter %), (Formula3 <= Parameter %)), "Growthing", If (OR(((( Formula 1 => Parameter %), (Formula 2 => Parameter %), (Formula3 => Parameter %)), "Decreasing", "No change"))
        1. % of Month Change (Current Month - Previous Month / Previous Month)
          • I need to able to filter the month I want then they will calculate based on the that month minus 1 month. (If I'm looking at January 2014 then it would compare to December 2013.)
        2. % Current Month Vs 2013 AVG (Current Month - 2013 AVG / 2013 AVG)
          • Filter will only apply to current month. 2013 will always be the full year average.
        3. % Current Month vs YTD  AVG 2014 (up to selected current month), (Current Month - YTD AVG 2014 / YTD AVG 2014)
          • As stated in the title I need the calculation to filter up to the month selected. So if i'm looking at April it would by April actual / YTD AVG April.