Need help in understanding FirstSortedValue in diagrams

[andreas_koehler]

Dear all,

I need to display the most frequent event per week.

The data are weeks with dates on which a certain event (A,B,C,D) occurs.

My understanding was that I create a simple table with 'week' as dimension and the following expression:

=FirstSortedValue({$}Event, - aggr(count({$} Event),Event))

I figured that the dimension would work like the 'group by' in the manual, so that I count the number of a specific event per week, sorted it descending per week and get the string of the most frequent event.

Turns out that is works when I define a week but that it doesnt work when no week is selected.

Why and what would be the solution?

I attached a little example for illustration.

As always: Thanks for any help and for your interest,

Andreas

[swuehl · Accepted solution]

Maybe

=FirstSortedValue({$}Event, - aggr(count({$} Event),Week,Event))

[swuehl]

If you have ties in your count of events per week, you can also use something like

=Concat( Aggr(If( Rank(Count(Event),1) = 1,Event),Week,Event),', ')

to show all events with the same max count.

[sunny_talwar]

Stefan is there a way we can use Concat with FirstSortedValue?

[andreas_koehler]

Thanks Stefan,

I guess you can hear my 'AGGR !!' when you open your window.

[swuehl]

Hi Sunny, not sure if I understand your question (wasn't there a similar question in a thread we both participated some weeks ago?), could you please elaborate?

[sunny_talwar]

You know how you used Concat with the Rank function to create more than one event (based on the count). I was wondering if we can somehow use the Concat with FirstSortedValue. I have tried doing this multiple times, but have never been successful. Is this something which is not possible to do, or am I missing something?

[swuehl]

Hi Sunny,

I was referring to

Re: FirstSortedValue()_Multiple dimension values with same count

Something like this is what you are looking for?

[sunny_talwar]

I am unable to replicate your orginial Concat(...Rank..)) expression. What am I missing here?

=FirstSortedValue( Aggr(Concat(DISTINCT Event, ','), Week, Event),-Aggr(Count(Event),Week, Event))

[swuehl]

Hi Sunny, you are referring the other thread's winning expression, right?

=FirstSortedValue( Aggr(Concat(DISTINCT EmployeeID, ','),YearlyIncome),-Aggr(YearlyIncome,YearlyIncome))

Note that the Concat() is already grouped by the ranking measure, something we would need to calculate (and that's hard to do in the aggr() dimensions...