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

[#1]

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!

[vgutkovsky · Accepted solution]

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