Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I suggest that you post your application or a representative sample. That way you will get more accurate and relevant help...
You can try if the DISTINCT qualifier resolves your issue:
firstsortedvalue(DISTINCT {$<Inv_YearMonth_Num= {$(=max(Inv_YearMonth_Num))}>}[Item Description],QtySold)
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
I replaced Unique Dimension with [Item Description], since [Item Description] i.e. the unique field and it works fine.
DISTINCT does not work in this case. Thanks a lot !!
Awesome
I am glad it worked for you.
Best,
Sunny