topic Aggr Count Help in QlikView

Aggr Count Help

Wed, 11 May 2011 13:28:26 GMT

Hi all,

I need an help with Aggr count, I have attached a sample & explained my requirement.

Thanks In Advance

Aggr Count Help

Wed, 11 May 2011 14:34:04 GMT

Your example doesn't really make a lot of sense. You already have the answer for the first column and I'm not sure how you are supposed to get 2 7's, a 5, and a 2 with those 10 rows loaded for the second column.....

Aggr Count Help

Wed, 11 May 2011 14:48:51 GMT

BRAND 2 is used by :

- ACCT A

- ACCT B (two times)

- ACCT C

ACCT A uses BRAND 1 and BRAND 2 : 2 brands

ACCT B uses BRAND 1 and BRAND 2 and BRAND 3 : 3 brands. Even if ACCT B uses 2 times BRAND 2, it have to be counted only 1 time.

ACCT C uses BRAND 2 and BRAND 3 : 2 brands

So, BRAND 2 is used by ACCT that use 2+3+2 = 7 brands.

This in an interesting problem...

For me, the "simpliest" solution is to calculate the expected values in the load script, creating specific tables / columns.

Aggr Count Help

Wed, 11 May 2011 17:08:37 GMT

Any help please....

Aggr Count Help

rbecher — Wed, 11 May 2011 17:17:25 GMT

=sum(aggr(count({1} distinct ACCT),[BRAND ID]))

- Ralf

Aggr Count Help

Wed, 11 May 2011 17:23:23 GMT

its not working....

Aggr Count Help

rbecher — Wed, 11 May 2011 17:25:26 GMT

Same result as in your table above! The COUNT is also correct (in table above is wrong).

Re: Aggr Count Help

Wed, 11 May 2011 17:55:28 GMT

I want an expression for "# Brands Purchased BY these accounts ". Please see attached image... hope you understand my question...

Aggr Count Help

rbecher — Wed, 11 May 2011 18:13:31 GMT

This makes no sense. Brands purchased by WHICH accounts?

Btw. the dimension is Brand not Account!

Re: Aggr Count Help

Wed, 11 May 2011 19:00:05 GMT

I clearly mentioned requirement in my attached files and also please look @ nmartin's reply...

Eg: for "BRAND 1"

# Accts bought "Brand 1" are 2 (Accts: A & B).

# Brands also bought (including Brand 1) by these accts are 5 (Distinct Brands from Acct A = 2 + Distinct Brands from Acct B = 3)

Note_1: Acct B bought BRAND 2 twice but It should consider it only once. i.e distinct brands by acct.

Note_2: User also have option to select Acct as Dimension, in that case how it is handled.

Re: Aggr Count Help

rbecher — Wed, 11 May 2011 20:01:42 GMT

You cannot break out of the dimension context (Brand).

Maybe the only solution is a precalculation of this figure as @nmartin wrote...

Re: Aggr Count Help

johnw — Thu, 12 May 2011 00:09:02 GMT

This seems to do the trick.

sum(aggr(count({<"BRAND ID"=>} total <"ACCT"> distinct "BRAND ID"),"BRAND ID","ACCT"))

Edit: Added set analysis to fix bug where if you selected a single row, the value changed.

Re: Aggr Count Help

rbecher — Thu, 12 May 2011 09:28:40 GMT

This is brilliant John!

How can this count work with a different dimensionality?

Re: Aggr Count Help

Thu, 12 May 2011 15:57:01 GMT

Thanks John... Perfect that's what I am looking for....

Re: Aggr Count Help

johnw — Thu, 12 May 2011 16:42:14 GMT

@TIQView - Ralf Becher wrote:
How can this count work with a different dimensionality?

Well, ignore the sum(), replace the aggr() with a table, and get rid of the set analysis for the moment. That gives us this hopefully more understandable case:

dimension 1 = BRAND ID
dimension 2 = ACCT

expression = count(total <"ACCT"> distinct "BRAND ID")

That's exactly how I started. Now, I wasn't certain it would work, but I thought it might, and it did. What seems to be happening is that since we have account after brand in the dimensions, we only look at the accounts for the brand for that row. However, in the count(), we tell it 'total <"ACCT">', which says to ignore the brand. It ends up paying attention to the brand for the selection of accounts, but then ignoring the brand for the count of brands. That's exactly what we needed. Then it's just a matter of converting this temporary table into an aggr() by putting our two dimensions in the aggr(), summing up, and finally using set analysis to make sure that when we select one specific brand, it doesn't only count that brand.