topic Need help in understanding FirstSortedValue in diagrams in QlikView

Need help in understanding FirstSortedValue in diagrams

andreas_koehler — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help in understanding FirstSortedValue in diagrams

swuehl — Wed, 01 Jun 2016 12:18:27 GMT

Maybe

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

Re: Need help in understanding FirstSortedValue in diagrams

swuehl — Wed, 01 Jun 2016 12:39:23 GMT

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.

Re: Need help in understanding FirstSortedValue in diagrams

sunny_talwar — Wed, 01 Jun 2016 12:41:36 GMT

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

Re: Need help in understanding FirstSortedValue in diagrams

andreas_koehler — Wed, 01 Jun 2016 12:42:05 GMT

Thanks Stefan,

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

Re: Need help in understanding FirstSortedValue in diagrams

swuehl — Wed, 01 Jun 2016 13:36:02 GMT

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?

Re: Need help in understanding FirstSortedValue in diagrams

sunny_talwar — Wed, 01 Jun 2016 13:39:11 GMT

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?

Re: Need help in understanding FirstSortedValue in diagrams

swuehl — Wed, 01 Jun 2016 13:48:22 GMT

Hi Sunny,

I was referring to

Re: FirstSortedValue()_Multiple dimension values with same count

Something like this is what you are looking for?

Re: Need help in understanding FirstSortedValue in diagrams

sunny_talwar — Wed, 01 Jun 2016 13:54:11 GMT

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))

Re: Need help in understanding FirstSortedValue in diagrams

swuehl — Wed, 01 Jun 2016 14:02:11 GMT

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...

Re: Need help in understanding FirstSortedValue in diagrams

sunny_talwar — Wed, 01 Jun 2016 14:06:13 GMT

I was wondering if that expression can be replicated in this thread's scenario. I think I won't over-populating this discussion and will create another thread of what I am thinking of doing.

Thanks Stefan

Best,

Sunny

Re: Need help in understanding FirstSortedValue in diagrams

andreas_koehler — Wed, 01 Jun 2016 15:49:46 GMT

Stefan,

thanks for this inspiration.

I used it to add expressions that concat and count the events that meet a certain threshold level of occurrences.

Andreas