topic Re: Issue on First Sorted Value in QlikView

Issue on First Sorted Value

bimala0507 — Thu, 20 Aug 2015 06:51:50 GMT

Dear experts:

I am having issue on the function firstsortedvalue. I am using the below expression, where it is trying to list the Item with least value sold. I am seeing there are many items with the same value and hence it shows NULL. Is there a way to pick up one from the List? How do I find out Only one amongst the whole list. I tried using Rank function, but it does not give me anything.

firstsortedvalue({$<Inv_YearMonth_Num= {$(=max(Inv_YearMonth_Num))}>}[Item Description],QtySold)

Appreciate a quick reply.

Kind Regards,

Bimala

Re: Issue on First Sorted Value

jonathandienst — Thu, 20 Aug 2015 06:57:22 GMT

I suggest that you post your application or a representative sample. That way you will get more accurate and relevant help...

Re: Issue on First Sorted Value

swuehl — Thu, 20 Aug 2015 07:06:06 GMT

You can try if the DISTINCT qualifier resolves your issue:

firstsortedvalue(DISTINCT {$<Inv_YearMonth_Num= {$(=max(Inv_YearMonth_Num))}>}[Item Description],QtySold)

Re: Issue on First Sorted Value

sunny_talwar — Thu, 20 Aug 2015 07:20:26 GMT

May be this:

=SubField(Concat(Aggr(FirstSortedValue({$<Inv_YearMonth_Num= {$(=Max(Inv_YearMonth_Num))}>}[Item Description],QtySold), uniqueDimension), '|'), '|', 1)

here uniqueDimension would be one which would give you a unique Item Description for each row.

HTH

Best,

Sunny

Re: Issue on First Sorted Value

bimala0507 — Thu, 20 Aug 2015 08:49:39 GMT

I replaced Unique Dimension with [Item Description], since [Item Description] i.e. the unique field and it works fine.

Re: Issue on First Sorted Value

bimala0507 — Thu, 20 Aug 2015 08:50:10 GMT

DISTINCT does not work in this case. Thanks a lot !!

Re: Issue on First Sorted Value

sunny_talwar — Thu, 20 Aug 2015 09:00:26 GMT

Awesome

I am glad it worked for you.

Best,

Sunny