3 Replies Latest reply on Nov 11, 2012 11:52 AM by Derk Busser

    How to treat this as a constant? Or something... Mixing agg/non-agg problem

    Annie Elliott

      I work off of a Daily Average Users number, and I have users per day. I can roll up these very easily for aggregated dates (Y, Q, M, W are all consistent), but not by day of week.

       

      SUM([DAU]) / 52 gives me numbers extremely consistent with the date-aggregated rollups as long as it is not the current year, because we are only 45 weeks into the year.

       

      When I try:

       

      SUM([ADI]) /

      (

      IF [Is Current Year] THEN

          DATEPART('week', NOW())

      ELSE 52

      END

      )

       

      I get errors about mixing aggregates and non aggregates. I need to be able to use DATEPART('week', NOW()) (currently = 45) as a constant for the divisor.

       

      I have tried wrapping it in INT() with no luck (same error message). Wrapping the date part in SUM() does as well. Any ideas?