Top Customer vs Top Products

[samp]

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'

];

[YoussefBelloum]

Hi,

customer as a dimension

expressions like this:

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

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

[sunny_talwar · Accepted solution]

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)

[samp]

top 2 customer names should be dynamic

[YoussefBelloum]

Ah ok ! so take Sunny's answer

[samp]

Hi Sunny,

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

[sunny_talwar]

Change this value

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

[samp]

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])