2 Replies Latest reply: Jun 19, 2012 12:13 PM by M Paeper RSS

    Aggregating as weekly a daily count field calculated with NODISTINCT

    M Paeper

      Hi,

       

      I have data as follows, the NoOps expression is NumericCount (distinct Operator)

       

      Operator is a machine operator on a production line and the DISTINCT is required to avoid double counting their number.

       

      The NoOps calc given is the correct answer for a particular CalendarDate.

       

      However, I cannot get the calc to aggregate correctly for the CalendarWeekEndingFriDate, it seems like the DISTINCT is being carried through to the weekly aggregation when I do get an answer that isnt Null in my formulae.

       

      I tried doing the NumericCount (distinct Operator) in the database LOAD statement and then aggregate the weeks on the desktop chart but that just gives me a script error during the load, and no further explanation.

       

      CalendarDateCalendarWeekEndingFriDateNo Ops
      10/05/201211/05/2012220
      11/05/201211/05/2012149
      14/05/201218/05/2012219
      15/05/201218/05/2012219
      16/05/201218/05/2012219
      17/05/201218/05/2012219
      18/05/201218/05/2012148
      21/05/201225/05/2012218
      22/05/201225/05/2012218
      23/05/201225/05/2012218
      24/05/201225/05/2012218
      25/05/201225/05/2012148
      28/05/201201/06/2012219
      29/05/201201/06/2012219
      30/05/201201/06/2012219
      31/05/201201/06/2012219
      01/06/201201/06/2012149
      04/06/201208/06/2012220
      05/06/201208/06/2012220
      06/06/201208/06/2012220
      07/06/201208/06/2012220
      08/06/201208/06/2012151
      11/06/201215/06/2012220
      12/06/201215/06/2012220
      13/06/201215/06/2012220
      14/06/201215/06/2012220

       

      As an example for the weekending 15/06 I would like to see (220+220+220+220)=880 as NoOps

      for weekending 08/06 I would like to see (151+220+220+220+220)= 1031 as NoOps

      etc

       

      How can I get these answers.

       

      I have tried:

      1. If I just have one dimension of CalendarWeekEndingFriDate in I get the same result for NoOps as shown in the table above.

      2. Trying Aggr(Sum(NumericCount (distinct Operator)),CalendarWeekEndingFriDate)

      or trying

      Aggr(Sum(NumericCount (distinct Operator)),CalendarDate)

      or trying

      Aggr(NODISTINCT Sum(NumericCount (distinct Operator)) ,CalendarDate)

      or trying

      Aggr(Sum(Count (distinct Operator)) ,CalendarDate)

      gives me - (I'm guessing thats a Null) in all the result fields

       

      Using QV11SR1

       

      Thanks