topic Re: Text Box: Show 1 out of Multiple Options in QlikView

Text Box: Show 1 out of Multiple Options

ciaran_mcgowan — Fri, 11 Apr 2014 09:28:51 GMT

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.

Re: Text Box: Show 1 out of Multiple Options

MK_QSL — Fri, 11 Apr 2014 09:33:00 GMT

Try using

FirstSortedValue(DISTINCT......

Re: Text Box: Show 1 out of Multiple Options

ciaran_mcgowan — Fri, 11 Apr 2014 09:41:44 GMT

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

Re: Text Box: Show 1 out of Multiple Options

MK_QSL — Fri, 11 Apr 2014 09:42:27 GMT

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

Re: Text Box: Show 1 out of Multiple Options

MK_QSL — Fri, 11 Apr 2014 09:48:38 GMT

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 .

Re: Text Box: Show 1 out of Multiple Options

ciaran_mcgowan — Fri, 11 Apr 2014 09:48:52 GMT

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!

Re: Text Box: Show 1 out of Multiple Options

nizamsha — Fri, 11 Apr 2014 10:08:07 GMT

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

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

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

Re: Text Box: Show 1 out of Multiple Options

nizamsha — Fri, 11 Apr 2014 10:17:03 GMT

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