Aggr Function

Karl_Hart — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm not entirely sure how to phrase this question, but here goes. I'll start with an example

Location Date Product Type

1 01/02/17 A

1 02/02/17 A

1 03/02/17 B

1 03/02/17 C

1 04/02/17 C

2 01/02/17 A

2 02/02/17 B

2 03/02/17 B

3 02/02/17 A

3 02/02/17 C

Lets say I want to add a new column that counts all the times that Product C has been sold in each location. I know I can do this with set analysis and it would give me

Location "C" Count

1 3

2 0

3 2

However, I would like this showing on every line when the table is fully expanded

Location Date Product Type "C" Count

1 01/02/17 A 3

1 02/02/17 A 3

1 03/02/17 B 3

1 03/02/17 C 3

1 04/02/17 C 3

2 01/02/17 A 0

2 02/02/17 B 0

2 03/02/17 B 0

3 02/02/17 A 2

3 02/02/17 C 2

I cant seem to get this to work becuase when I add the individual lines, it does the calcualtion line by line,not at the Location level.

I've tried a number of different combinations of

Sum(aggr(Count(Distinct {<[Product Type]={'C'}>} ProductKey),Location))

but none seem to give the desired results.

I appreciate any advice on this

Thanks

Re: Aggr Function

sunny_talwar — Tue, 05 Dec 2017 12:33:40 GMT

Try this

Count(TOTAL <LOCATION> {<[Product Type]={'C'}>} ProductKey)

Re: Aggr Function

Anil_Babu_Samineni — Tue, 05 Dec 2017 12:34:59 GMT

May be this?

Sum(Aggr(Count(TOTAL <LOCATION> {<[Product Type]={'C'}>} ProductKey), Location))

topic Aggr Function in QlikView

Aggr Function

Re: Aggr Function

Re: Aggr Function