Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

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)