Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
Count(TOTAL <LOCATION> {<[Product Type]={'C'}>} ProductKey)
May be this?
Sum(Aggr(Count(TOTAL <LOCATION> {<[Product Type]={'C'}>} ProductKey), Location))