Problem with AGGR function - it changes value based on range?

[#1]

Hi

I am seeing the following expression value change as I filter between different ranges (dimenson) of time? - how do I get the formula to be consistent regardless of what the time dimension is?

Background:

By month, I wish to calculate how many calls (TTH_CALL_ID) only appear once - so I wrote the following expression:

=sum(if(aggr(count(TTH_CALL_ID),TTH_CALL_ID)=1,1,0))

However, I have just noticed that as I change the range of months (i.e. from Jan to Jun to just March) - the value shown in March is differs?

i..e During Jan to Jun the March value is 11

But when I just drill into March alone, the value is 14 (correct)

What do I put into the expression to ensure that it restricts by the dimension too?

(I hope this is clear!)

Thanks

Alun

[rwunderlich]

Try adding NODISTINCT to the aggr

=sum(if(aggr(NODISTINCT count(TTH_CALL_ID),TTH_CALL_ID)=1,1,0))

-Rob

[#3]

Rob

Thanks for feedback - unfortunately, adding NODISTINCT resulted in no rows returned?

I guess I can't understand as to why the expression value changes when altering the dimension? I would have expected the expression to work at the level of the dimension being set - but this does not appear to be the case (as when the time dimension is broadened, the aggr expression evaluates the full set of data and not just those rows relevant to the dimensions).

Regards

Alun

[#4]

What are you trying to accomplish with the expression? It looks like you are trying to get a count of the values in TTH_CALL_ID, but I'm confused about the purpose of the aggr if you are counting the same field you are trying to nest the aggregation across.

[#5]

Hi Aron

What I am trying to do is count for a specific column, those records that only appear once:

=sum(if(aggr(count(TTH_CALL_ID),TTH_CALL_ID)=1,1,0))

This is not the same as counting DISTINCT as I only wish to count those values that only appear once (i.e. a value could appear multiple times and therefore I am not interested in counting these).

Maybe I should have been clearer at the start to ask how to write the expression i needed!

Appreciate any insights you could provide.

Thanks in advance (Causing me many sleepless nights!)

Alun

[#6]

Ok, I think I understand what you are trying to do. I think you may need to switch your aggr and if functions like this.

sum(aggr(if(count(TTH_CALL_ID)=1, 1), TTH_CALL_ID))

This way the entire if function is being evaluated for each TTH_CALL_ID value.

[#7]

Aaron

Thanks for the input - but this expression returns the same value as my current one.

I think I may have misunderstood the principles by which Qlikview works. I had assume that the dimensions were evaluated prior to the expression being calculated and that the expression would be calculated on the sub sets of data as defined by the dimension groupings.

Here is an example of what I am trying to achieve:

Jan 123
Jan 456
Jan 456
Jan 000
Jan 123
Feb 789
Feb 123
Feb 789
Feb 999

Using above, I would like to calculate by month what numbers only appear once. So I the result set that I am after is:

Jan 2 - as only numbers 123 & 000 appear once in Jan
Feb 2 - as only numbers 123 & 999 appear once in Feb

Now when I use my expression (or yours) and use month as a dimension - I only get the right results when I have drilled into a specific month.

When I look at all the data, I get the following result:

Jan 1 - as only 000 appears once across all months
Feb 1 - as only 999 appears once across all months

Maybe I have gone about this the wrong way - can you think of a way to achieve this so that the expression is evaluated within the a specific month (dimension) regardless of the range of data selected across months?

Thanks

Alun

[#8]

CORRECTION: In the data set, one of the rows displaying Jan 123 should have been removed.

[#9 · Accepted solution]

I think you just need to embed the month field in the aggr as well otherwise the count will be checking for duplicate IDs across all months.

sum(aggr(if(count(TTH_CALL_ID)=1, 1), MONTH_FIELD, TTH_CALL_ID))

[#10]

Great feedback Aaron - it worked.

Thanks for input!!!!

Alun