Text Box: Show 1 out of Multiple Options

[ciaran_mcgowan]

Hi,

I have a text box that displays a sentence about a top Sales Person, which works fine until dates are drilled into to display a smaller date range; I know what the problem is, just not the solution. The problem is that occasionally, more than one Sales Person will have the exact same data with absolutely nothing to distinguish them (ie. Sales Amount, Sales Quantity, Hours Worked, Profit Margin, etc). How can I sent the text box to display either (1) the first Sales person alphabetically or (2) all Sales people?

My first attempt looked like this:

= 'Top Sales person for ' & vTimePeriod & ' goes to ' &

FirstSortedValue(SalesPerson, -(Aggr(Max(SalesAmount), SalesPersonID)))

& ' with '&

FirstSortedValue(SalesAmount, -(Aggr(Max(SalesAmount), SalesPersonID)))

& '!'

But there were too many Sales people being returned so I altered it to display the Sales Person who (1) Made the most money, (2) Sold the most items IN (3) The shorted time. This cut down on duplication caused by the above calculation.

= 'Top Sales person for ' & vTimePeriod & ' goes to ' &

FirstSortedValue(SalesPerson, -(Aggr(Max(SalesAmount)+(Quantity/1000)+(Hours/1000), SalesPersonID)))

& ' selling '&

FirstSortedValue(Quantity, -(Aggr(Max(SalesAmount)+(Quantity/1000)+(Hours/1000), SalesPersonID)))

& ' valued at '&

FirstSortedValue(SalesAmount, -(Aggr(Max(SalesAmount)+(Quantity/1000)+(Hours/1000), SalesPersonID)))

& 'in a ' &

FirstSortedValue(Hours, -(Aggr(Max(SalesAmount)+(Quantity/1000)+(Hours/1000), SalesPersonID)))

& 'period!'

It's crude but it works for the most part. If anyone has any thoughts on how I can get this over the line, I'd greatly appreciate it.

[MK_QSL · Accepted solution]

Try using

FirstSortedValue(DISTINCT......

[ciaran_mcgowan]

That was quick! But unfortunately you can't use DISTINCT() with FirstSortedValue().

[MK_QSL]

You can use Distinct..... It will give you syntax error but believe me, you can use....!

[MK_QSL]

I had the same question few months ago...

Rob Wunderlich has replied me as below...

DISTINCT appeared in the doc some time back (I think an early QV10 SR) but the syntax checker was never updated. (or may have worked for a while -- can't recall). The red squiggle is probably why most people don't know about DISTINCT in FirstSortedValue .

[ciaran_mcgowan]

Thank you so much for this! This will actually help a lot with some of my older applications too! DISTINCT was the first thing I tried using in cases like these but I never saved it because of the syntax errors!

Now I know that syntax errors don't always mean bad equations!

[nizamsha]

U can Try This One also u will also get the desired result

=FirstSortedValue(SalesPerson,-Aggr(sum(Salesamount),SalesPersonID))  & ' - ' &

Max( Aggr(sum(Salesamount),SalesPersonID))

[nizamsha]

Try this one to it will also bring the top sales person and salesamount

=FirstSortedValue(SalesPerson,-Aggr(sum(Salesamount),SalesPersonID))  & ' - ' &

Only( if( Aggr(rank(sum(Salesamount)),SalesPersonID)=1,Aggr( sum(Salesamount),SalesPersonID)))