Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Aggr Function

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     B

1             03/02/17     C

1              04/02/17     C

1              04/02/17     C

2              01/02/17     A

2              02/02/17     B

2              02/02/17     B

2              02/02/17     B

2              03/02/17     B

3              02/02/17     A

3              02/02/17     C

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     B                              3

1             03/02/17     C                              3

1              04/02/17     C                              3

1              04/02/17     C                              3

2              01/02/17     A                              0

2              02/02/17     B                              0

2              02/02/17     B                              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

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

1 Solution

Accepted Solutions
Highlighted

Try this

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

View solution in original post

2 Replies
Highlighted

Try this

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

View solution in original post

Highlighted

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)