Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dennisnet
Valued Contributor III

Showing the data that with the higest count

Dear specialist.

I got data of played songs and artists.

Here is what I got:

Fields:

UTS           (=song ID)

Artists

Month

Dimension : Month

Expressions :

1. Count (Distinct UTS)

2. MAX(aggr((Count (Distinct UTS)),Artist,Month))

The first expression counts the number of song

The second expression gives the count of the songs of the Artist with the most songs, that month.

fe

                    1                         2                         3

Jan          2362                         34                    U2

Feb          3335                        78                    Prince

...

So in this example U2 is played the most in Januari (34 times) and Prince (78 times) in Februari.

What I want is an expression that returns (3) the name of the artist that has the highest count of songs in the straight/pivot table.

1 Solution

Accepted Solutions
MVP
MVP

Re: Showing the data that with the higest count

Try something like

=FirstSortedValue(Artist, aggr(-count(distinct UTS),Artist, Month))

4 Replies
MVP
MVP

Re: Showing the data that with the higest count

Try something like

=FirstSortedValue(Artist, aggr(-count(distinct UTS),Artist, Month))

dennisnet
Valued Contributor III

Re: Showing the data that with the higest count

Thanks but that returns nothing...

MVP
MVP

Re: Showing the data that with the higest count

It does work at my side, please check attached.

Maybe your setting is different from what you described or I understood you have?

dennisnet
Valued Contributor III

Re: Showing the data that with the higest count

Sorry my mistake! Typo :-)

Works perfect. Thanks