Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Aggregating as weekly a daily count field calculated with NODISTINCT

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregating as weekly a daily count field calculated with NODISTINCT

What about

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

2 Replies
MVP
MVP

Re: Aggregating as weekly a daily count field calculated with NODISTINCT

What about

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

Not applicable

Re: Aggregating as weekly a daily count field calculated with NODISTINCT

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.

Community Browser