6 Replies Latest reply on Sep 24, 2013 10:07 AM by Joshua Milligan

Add Region as Color without affecting rank

josh.surette Sep 23, 2013 2:03 PM

Hi,

In the attached workbook, is it possible to add region to the color pill without changing the rank? Ultimately, I'd like to see the same top n rank, but just add region to show the breakout of sales for that particular product name. When I add region as a color, it changes the rank order. If I add region to the column shelf, it ranks correctly; however, I'd like to limit the results to a stacked bar.

Any help would be much appreciated!

Rank_Test.twb.zip 38.7 KB

1. Re: Add Region as Color without affecting rank

Michel Caissie Sep 23, 2013 2:49 PM (in response to josh.surette)

Josh,
What you need is to change the Adressing of your Rank Table Calculation. This way you will give a single rank for every Product without restarting the ranking for every region having sales on the product.

You can also change the order of the region by right-clicking on the Region pill and set the Sort options.

Michel
ps: next time try to post a packaged workbook (.twbx) instead of a .twb. even if you use the Superstore.xls. You will increase your chances to get an answer
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Re: Add Region as Color without affecting rank

josh.surette Sep 23, 2013 3:29 PM (in response to Michel Caissie)
Thanks for the reply, Michel! I've tried that several times, but it still results in a different rank order than when region is omitted from the color pill. Does this process have to be done in a specific order? I feel like I'm missing a step here.

I've attached the packaged workbook. Thanks for the tip!

Really appreciate the help!
Josh

Rank_Test.twbx.zip 1.1 MB
Like Show 0 Likes(0)

Actions
3. Re: Re: Re: Add Region as Color without affecting rank

Joshua Milligan Sep 23, 2013 7:29 PM (in response to josh.surette)
Josh,

Michel's approach should work. Is it possible that you've got a different setup on the filter calculation?

If you just want to filter to the Top N, there are some other options:
You can also use a calculated Set to accomplish your goal here. If you right click the Product field you can select "Create Set" and then use the Top tab to create a set based on the Top N products by Sales. You'll have to use an additional calculated field to reference the set and use in the filter.
You can use a Top N filter on the products field. That's probably the easiest approach.

I've attached a workbook to demonstrate Michel's approach and the other possibilities.

Regards,
Joshua

Rank_Test.twbx.zip 419.1 KB
Like Show 0 Likes(0)

Actions
4. Re: Re: Re: Add Region as Color without affecting rank

Joshua Milligan Sep 23, 2013 7:31 PM (in response to josh.surette)

By the way, 8.1 will have a nice RANK function that will make life a little easier!
Like Show 0 Likes(0)

Actions
5. Re: Add Region as Color without affecting rank

josh.surette Sep 24, 2013 7:37 AM (in response to Joshua Milligan)

Excellent! Thanks so much for the reply. The trick was leaving the Sort field "Automatic" on the advanced settings. If I tried to use a specific measure instead of automatic, it would throw the sort off. I really appreciate the help from both of you!

Also, thanks for the heads up regarding the new Rank function coming. I'll be sure to check it out in 8.1.

Thanks again!
Josh
Like Show 0 Likes(0)

Actions
6. Re: Add Region as Color without affecting rank

Joshua Milligan Sep 24, 2013 10:07 AM (in response to josh.surette)

You're welcome!
Like Show 0 Likes(0)

Actions