topic Re: First Sorted Value - help with expression in QlikView

First Sorted Value - help with expression

ljackson — Tue, 17 Dec 2013 12:18:11 GMT

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

Re: First Sorted Value - help with expression

ljackson — Wed, 26 Feb 2014 13:55:26 GMT

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

Re: First Sorted Value - help with expression

jerem1234 — Wed, 26 Feb 2014 14:13:17 GMT

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?

Re: Re: First Sorted Value - help with expression

ljackson — Wed, 26 Feb 2014 15:08:27 GMT

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.

Re: Re: Re: First Sorted Value - help with expression

jerem1234 — Wed, 26 Feb 2014 15:21:05 GMT

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!

Re: First Sorted Value - help with expression

ljackson — Wed, 26 Feb 2014 15:29:21 GMT

Fantastic, thank you so much.

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