7 Replies Latest reply: Nov 16, 2017 3:30 PM by sam pusala

# Top Customer vs Top Products

I am planning to achieve below 4th table for Top Customer vs Top Products.

Example:  have simple below 3 table charts results for sample Inline data added bottom.

All data in simple tables:

Expected result:

sample data:

custTable:
Load * Inline [
customer, product

'abc', 'p1'
'abc',  'p1'
'abc',  'p1'
'abc', 'p2'
'abc', 'p3'

'pqr',  'p1'
'pqr', 'p2'
'pqr',  'p2'
'pqr', 'p3'

'xyz', 'p5'
'EEE', 'p1'

];

• ###### Re: Top Customer vs Top Products

Hi,

customer as a dimension

expressions like this:

1st: Count(   {<customer= {'abc'}  >}  product) ==> for TOP1 column

2nd: Count(   {<customer= {'pqr'}  >}  product) ==> for TOP2 column

• ###### Re: Top Customer vs Top Products

top 2 customer names should be dynamic

• ###### Re: Top Customer vs Top Products

Ah ok ! so take Sunny's answer

• ###### Re: Top Customer vs Top Products

Try these expression

1)

Count({<product = {"=Rank(Count(product)) < 3"}>} product)

2)

Count({<product = {"=Rank(Count(product)) < 3"}, customer = {"=Rank(Count({<product = {""=Rank(Count(product)) < 3""}>} product)) = 1"}>} product)

3)

Count({<product = {"=Rank(Count(product)) < 3"}, customer = {"=Rank(Count({<product = {""=Rank(Count(product)) < 3""}>} product)) = 2"}>} product)

• ###### Re: Top Customer vs Top Products

Hi Sunny,

working like charm, can you suggest me hoe to add names dynamically in the 3rd (abc)and 4th(pqr) headers?

• ###### Re: Top Customer vs Top Products

Change this value

Count({<product = {"=Rank(Count(product)) < 3"}, customer = {"=Rank(Count({<product = {""=Rank(Count(product)) < 3""}>} product)) = 1"}>} product)

• ###### Re: Top Customer vs Top Products

Hi Sunny.

for your first line:

1) Count({<product = {"=Rank(Count(product)) < 3"}>} product)

I am trying to add my additional filters for your given exp seems syntax is correct but I am not getting expected results, is below exp correct? thanks for your help.

Count({<[Investment Strategy] = {"=Rank(Count({<Type = {'RFI','Database Search','Database Search Follow-Up'},Quarter={'Q1','Q2','Q3'},Year={""\$(=Max(Year))""}>} [Investment Strategy]))<11" } > } [Investment Strategy])