1 2 Previous Next 23 Replies Latest reply on Aug 15, 2017 6:42 PM by Shinichiro Murakami

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Hi all,

I am having a problem ranking only partial dimension while showing all. I want to show all years, but rank only most current year, most current quarter, etc . so I am showing variances per specialty cost.

Variances are part of table calculations. Now, I created Top N parameter to show TOP N , and then bottom N based on variance only for current year ( 2017). I still want to show all years, but only rank 2017 ( and select TOP N based on 2017 data).

then I want to show it also Quarterly comparison starting most current complete quarter of most current year. And select TOP N and bottom N.

What is best approach to this?

thank you so much!

• 1. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Seed attached.

• 2. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

What I did in there:

On your original sheet I created a calc called index, and added it to text.  It shows that you can identify what the last column is, no matter if you have just years, or quarters too (or months or weeks or days...)

I also added a calc called MAX index.  This tells you what that max number is.  (Note:  You don't really need these interim calcs, but I compartmentalized to show steps.)

I made a calc called Last value.  If index-maxIndex, then you have that last column.  Grab that value.  For kicks I added another calc to put it in all the columns.  Just so you can see how it behaves.

Now go to sheet 3.

I Ranked that last column value.  Initially I put it on text to prove to myself that it worked.  I have to change the table calc direction for this one to TABLE_DOWN.  (All the rest do table across, and rightly so,  But I want to look at these values going down the sheet.)  Then, to make it sort properly, I changed it to DISCRETE, and then dragged it to the front of the ROWS shelf.  To see what it does elsewhere, drag behind [SPCLTY-CD] on ROWS.

Because it is the first pill on ROWS, Tableau uses it to sort first.  And by function of tableau, it sort is ascending.  that's all Tableau will ever do with a table calc there - ascending.  (If you ever want to do the same thing, but sort descending, create a second calc that multiplies the value by -1 and put THAT on the front of ROWS.)

I had to take other table calc filters off the sheet.  Table calc filters don't filter out rows from the table.  they only filter out what part of the table gets displayed.  If you put the filters back on, you will still see the calc ranking in the first column, but numbers will be missing.  That's because they're still in the table, and they get included in the RANK table calc (and any other table calc.)

• 3. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Hi Indre

That's quite a bit challenging...

In these case, using LOD is easier to sort/filter, but you already have complicated formula and LOD is most likely not available.

You can hide left end header of Rank window.

So, I needed to use table cal, but it requires appropriately set 5 layers.

1.  Compare current - last to calc Delta

2.  Rank Delta

3.  index with time horizon

4.  Rank with index()

5.  Share 4's rank across time horizon

You can use same logic across two sheets though.

Thanks,

Shin << Ambassador Spotlight Here ! >>

• 4. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important;  padding-left:1ex !important; background-color:white !important; } hi  Shinichiro,

Thank you so much for this great solution and explanation. It helps a lot. I am reviewing yearly calculations - and seems it gets stuck on TOP 6 - is that because of zeros? I need to exclude zeros and continue to negative variances.However- I will need to rank top N negative variances, so I need to adjust this if last()=0 then  end for both? if i edit formula if last() >0 or last

Sent: Friday, August 11, 2017 3:04 PM

Subject: Re:  - Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

#yiv3349557749 * #yiv3349557749 a #yiv3349557749 body {font-family:Helvetica, Arial, sans-serif;}#yiv3349557749 h1, #yiv3349557749 h2, #yiv3349557749 h3, #yiv3349557749 h4, #yiv3349557749 h5, #yiv3349557749 h6, #yiv3349557749 p, #yiv3349557749 hr {}#yiv3349557749 .yiv3349557749button td {}

|

 Tableau Community

|

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Rank  Time Dimension by current year by Calculated Fields(with Table Calculations) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

• 5. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

sorry the images got distorted when I replied through email, will try attaching here. thanks for your help.

From: Shinichiro Murakami <tableaucommunity@tableau.com>

To: Indre White <indrewhite@yahoo.com>

Sent: Friday, August 11, 2017 3:04 PM

Subject: Re:  - Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

#yiv4007047095 * #yiv4007047095 a #yiv4007047095 body {font-family:Helvetica, Arial, sans-serif;}#yiv4007047095 #yiv4007047095 h1, #yiv4007047095 h2, #yiv4007047095 h3, #yiv4007047095 h4, #yiv4007047095 h5, #yiv4007047095 h6, #yiv4007047095 p, #yiv4007047095 hr {}#yiv4007047095 .yiv4007047095button td {}

|

 Tableau Community

|

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Rank  Time Dimension by current year by Calculated Fields(with Table Calculations) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

• 6. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Hi Indre

I'm not sure you could solve your problem or not.

If you are OK with previous answer, could you mark my answer as correct to close the thread.

To mark "correct", you need to go to original post in the community, not from email view.

Thanks,

1 of 1 people found this helpful
• 7. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

hi Shinichiro,  thank you, I would like to finish this- as right now  the ranking stops at zero (if you look at the sheet ranking by year), and I need to skip zeros and be able to rank Top 10 ( after skipping zero variances), and it stops at rank 5. So I cannot select Top 10 with current formula?Or If I need to rank descending? Would you please help me  finalize this? thank you so much

From: Shinichiro Murakami <tableaucommunity@tableau.com>

To: Indre White <indrewhite@yahoo.com>

Sent: Saturday, August 12, 2017 9:11 PM

Subject: Re:  - Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

#yiv8165409464 * #yiv8165409464 a #yiv8165409464 body {font-family:Helvetica, Arial, sans-serif;}#yiv8165409464 #yiv8165409464 h1, #yiv8165409464 h2, #yiv8165409464 h3, #yiv8165409464 h4, #yiv8165409464 h5, #yiv8165409464 h6, #yiv8165409464 p, #yiv8165409464 hr {}#yiv8165409464 .yiv8165409464button td {}

|

 Tableau Community

|

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

reply from Shinichiro Murakami in Forums - View the full discussionHi Indre I'm not sure you could solve your problem or not.If you are OK with previous answer, could you mark my answer as correct to close the thread.To mark "correct", you need to go to original post in the community, not from email view. Thanks,Shin << Ambassador Spotlight Here>>

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Rank  Time Dimension by current year by Calculated Fields(with Table Calculations) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

• 8. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Hi Indre

Rank_unique , instead of "Rank" for field "Rank  Variance PMPM".

Thanks,

1 of 1 people found this helpful
• 9. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

hi, thanks for your help, I wanted to mark this as correct answer - however I do not see how to mark answer correct. It used to be under actions. Now, when I click on actions - shows no actions available.

• 10. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

You need to go to original post not from the inbox view.

Thanks,

Shin

• 11. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

hi Shinichiro, one more question - how do I rank descending on  last Quarter variances starting with highest negative variance? thank you so much

From: Shinichiro Murakami <tableaucommunity@tableau.com>

To: Indre White <indrewhite@yahoo.com>

Sent: Saturday, August 12, 2017 9:11 PM

Subject: Re:  - Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

#yiv5757003506 * #yiv5757003506 a #yiv5757003506 body {font-family:Helvetica, Arial, sans-serif;}#yiv5757003506 #yiv5757003506 h1, #yiv5757003506 h2, #yiv5757003506 h3, #yiv5757003506 h4, #yiv5757003506 h5, #yiv5757003506 h6, #yiv5757003506 p, #yiv5757003506 hr {}#yiv5757003506 .yiv5757003506button td {}

|

 Tableau Community

|

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

reply from Shinichiro Murakami in Forums - View the full discussionHi Indre I'm not sure you could solve your problem or not.If you are OK with previous answer, could you mark my answer as correct to close the thread.To mark "correct", you need to go to original post in the community, not from email view. Thanks,Shin << Ambassador Spotlight Here>>

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Rank  Time Dimension by current year by Calculated Fields(with Table Calculations) in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

• 12. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

==> rank_unique(Variance  \$ PMPM,'desc')

Thanks,

Shin

1 of 1 people found this helpful
• 13. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Thanks so much. I redid all calculations for ranking descending order. However, I am getting the view ( see attached), what did I do wrong? I did use rank_unique, but I got multiple specialties within each of the ranks.

thanks a lot

From: Shinichiro Murakami <tableaucommunity@tableau.com>

To: Indre White <indrewhite@yahoo.com>

Sent: Sunday, August 13, 2017 4:29 PM

Subject: Re:  - Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

#yiv3896298919 * #yiv3896298919 a #yiv3896298919 body {font-family:Helvetica, Arial, sans-serif;}#yiv3896298919 #yiv3896298919 h1, #yiv3896298919 h2, #yiv3896298919 h3, #yiv3896298919 h4, #yiv3896298919 h5, #yiv3896298919 h6, #yiv3896298919 p, #yiv3896298919 hr {}#yiv3896298919 .yiv3896298919button td {}

|

 Tableau Community

|

Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

reply from Shinichiro Murakami in Forums - View the full discussion https://community.tableau.com/servlet/JiveServlet/downloadImage/2-653380-321544/pastedImage_0.png  ==> rank_unique(Variance  \$ PMPM,'desc') Thanks,Shin

 Reply to this message by replying to this email, or go to the message on Tableau Community Start a new discussion in Forums by email or at Tableau Community Following Rank  Time Dimension by current year by Calculated Fields(with Table Calculations) in these streams: Inbox Following Shinichiro Murakami in these streams: Inbox

 This email was sent by Tableau Community because you are a registered user. You may unsubscribe instantly from Tableau Community, or adjust email frequency in your email preferences

1 of 1 people found this helpful
• 14. Re: Rank  Time Dimension by current year by Calculated Fields(with Table Calculations)

Indre,

As I said, required table calculation is VERY complicated.

Follow all the steps Exactly as same as my previous attachment.

One difference brings completely different results.

Thanks,

Shin

1 2 Previous Next