Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

How to craft an expression? (count of top rankings)

Here's what I'm trying to do, as simplified as I can explain it:

Relevant fields:

Dims = Week, Territory

Expression = Sum(Units)

I would like to display:

For each Territory, show the # of Weeks where that Territory was #1 in units.

So, for example, I've currently created a grid that shows data something like this:

Week,Top Territory,Units

Week 1,Terr 1,500

Week 2,Terr 2,550

Week 3,Terr 2,490

Week 4,Terr 5,650

Week 5,Terr 3,800

Week 6,Terr 5,750

Week 7,Terr 5,800

(to do this, I just did a Dimension Limit on Territory, "Show Only Largest = 1")

Elsewhere I'd like to display the expression I described above. The values I'd hope to see would be:

Territory,Weeks Ranked #1

Terr 1, 1

Terr 2, 2

Terr 3, 1

Terr 4, 0

Terr 5, 3

Any ideas on how I can craft the expression for "Weeks Ranked #1" as shown?

Thanks!

1 Solution

Accepted Solutions
vgutkovsky
Honored Contributor II

Re: How to craft an expression? (count of top rankings)

The chart dimension should just be Territory. The expression can be something like:

sum(aggr(

       if(rank(sum(Sales),4,1)=1,1)

       ,Week,Territory

))

Regards,

Vlad

1 Reply
vgutkovsky
Honored Contributor II

Re: How to craft an expression? (count of top rankings)

The chart dimension should just be Territory. The expression can be something like:

sum(aggr(

       if(rank(sum(Sales),4,1)=1,1)

       ,Week,Territory

))

Regards,

Vlad