First Sorted Value - help with expression

[ljackson]

I have the following dimensions:

Customer (this is 5 digit numeric)

Sales_Rep

Top_100_Product

I want to find out, for every Sales Rep, which customer is stocking the most Top 100 products.   There are several products linked to each 'top 100 product' so I have tried to count by distinct 'top 100 product'. 

FirstSortedValue(Customer,-aggr(Count(DISTINCT [Top 100 Product]),[Sales_Rep]))

The above expression returns the highest customer numerically in the list for each Sales Rep, not the one with the most Top 100 Products.

Can anyone see what I'm doing wrong?

Thanks

.

[ljackson]

This is still an issue, I am struggling to find the right expressions to show, for each sales rep, which customer is stocking the most Top_100_Products.

Can anyone help me?

Thank you

[jerem1234]

What does:

FirstSortedValue(Customer,-Count(DISTINCT [Top 100 Product]))

get you?

If you could post a sample app demonstrating your problem and what your data looks like, that would be helpful. Are you using this in a straight table, what are the dimensions in your chart?

[ljackson]

When using that expression I get null values.

I have created a dummy file (simplified) with identical dimensions/expressions.  I would appreciate if you could take a look.

Thanks again.

[jerem1234 · Accepted solution]

You almost had it:

FirstSortedValue([c-iname],-aggr(Count(DISTINCT [Top 100 Product]), [c-iname]))

For the expression, I didn't know if you wanted both customer and code, so I concatenated them like:

FirstSortedValue(CustCodeH,-aggr(Count(DISTINCT [Top 100 Product]), CustCodeH)) & ' ' & FirstSortedValue([c-iname],-aggr(Count(DISTINCT [Top 100 Product]), [c-iname]))

Please find atached.

Hope this helps!

[ljackson]

Fantastic, thank you so much.

I knew I was close, just couldn't fit the final jigsaw piece into place!

.