1 Reply Latest reply on Jun 6, 2013 7:40 AM by Jim Wahl

    Getting a count of Above and Below Averages.

    Rob Strong

      I have this calculation that gives me which store is above and below based on revenue:

      IF SUM( [NET_REVENUE]) > WINDOW_AVG(SUM([NET_REVENUE])) THEN "Above average"

      ELSEIF SUM([NET_REVENUE]) < WINDOW_AVG(SUM([NET_REVENUE])) THEN "Below average"

      ELSE "Average"

      END

       

      What I need to do is take this and count the total stores in each category.  So I wrote this

      IF [AboveOrBelowAverage] = "Above average" then COUNTD([CLINIC_EXT_ID] ) end

      But it isn't working the way I thought it would.

      Oh and I tried this Boolean too but to no avail: AVG([NET_REVENUE]))) > TOTAL(AVG([NET_REVENUE]))

      I know I am missing something simple, and I am going to hate myself when someone points out what it is,  but for the life of me I can't see it.

      I would post the workbook, but it has client data in it and the extract is huge.

      Thanks!