Issue on First Sorted Value

[bimala0507]

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

[jonathandienst]

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

[swuehl]

You can try if the DISTINCT qualifier resolves your issue:

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

[sunny_talwar · Accepted solution]

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

[bimala0507]

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

[bimala0507]

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

[sunny_talwar]

Awesome

I am glad it worked for you.

Best,

Sunny