Aggr Function

[Karl_Hart]

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

[sunny_talwar · Accepted solution]

Try this

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

[Anil_Babu_Samineni]

May be this?

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