Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master II
Master II

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

View solution in original post

1 Reply
vgutkovsky
Master II
Master II

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