How to get the Value of a Sorted First Value

[CamiloGrau]

Hello,

I'm using FirstSortedValue, to get the most common Reason for product returns by store and area  by count of documents in a chart.

This seems to work fine:

FirstSortedValue(Reason, -aggr(count(Distinct Document),Reason, Store, Area))

I'm getting something like this:

Store	Area	FirsSortedValueReason
St. 1	A1	Reason 1
St. 1	A2	Reason 1
St. 2	A1	Reason 3
St. 2	A2	Reason 2
St. 2	A3	Reason 4

What i just can't figure out is how to get the value it's evaluating. I mean for example, if by count of documents, the most common Reason for Store 1, Area 2  is Reason 1, ¿How do i get "that" count of documents for every line?

Please Help me!

Best Regards

[agigliotti]

Hi @CamiloGrau ,

Why not simple "=count(Distinct Document)" as expression ?

I hope it can helps.

Best Regards

[CamiloGrau]

Hi @agigliotti , 

This is not the case, as i first want to get the most common reason  grouped by Store and Area, and then i need the number of documents that made that value the first sorted one.

Best Regards

[Saravanan_Desingh]

If the Dimension is Store and Area, then expression is Count(DISTINCT Document).

If not, try something like

Count(Distinct Aggr(Document, Store, Area))

[MayilVahanan]

Hi @CamiloGrau 

Try like this

Max(aggr(count(Distinct Document),Reason, Store, Area))

<-- give the max count of document based on Reason, Store, Area ..
And, for recon , you can create the table,

Reason, Store, Area as Dim &

Count(Distinct Document) as measure

to verify whether its correct or not.