Aggregating as weekly a daily count field calculated with NODISTINCT

[#1]

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.

CalendarDate	CalendarWeekEndingFriDate	No Ops
10/05/2012	11/05/2012	220
11/05/2012	11/05/2012	149
14/05/2012	18/05/2012	219
15/05/2012	18/05/2012	219
16/05/2012	18/05/2012	219
17/05/2012	18/05/2012	219
18/05/2012	18/05/2012	148
21/05/2012	25/05/2012	218
22/05/2012	25/05/2012	218
23/05/2012	25/05/2012	218
24/05/2012	25/05/2012	218
25/05/2012	25/05/2012	148
28/05/2012	01/06/2012	219
29/05/2012	01/06/2012	219
30/05/2012	01/06/2012	219
31/05/2012	01/06/2012	219
01/06/2012	01/06/2012	149
04/06/2012	08/06/2012	220
05/06/2012	08/06/2012	220
06/06/2012	08/06/2012	220
07/06/2012	08/06/2012	220
08/06/2012	08/06/2012	151
11/06/2012	15/06/2012	220
12/06/2012	15/06/2012	220
13/06/2012	15/06/2012	220
14/06/2012	15/06/2012	220

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

[swuehl · Accepted solution]

What about

=sum(total<CalendarWeekEndingFriDate> aggr( count( distinct Operator), CalendarWeekEndingFriDate, CalendarDate))

[#3]

Genius! 🙂 Thanks very much, thats 100% what I wanted to see. I was just in the RTFM looking up the total function to decide if that might assist and you've saved me a whole bunch of trial and error. Of course my original post should actually have read calc using DISTINCT but you figured that out easily. Thank you.