3 Replies Latest reply on Jul 10, 2018 11:34 AM by Shinichiro Murakami

# Calculating TotalSum per dynamically calculated Ranks

Hi All,

I am trying to make use of Tableau's Table calculations functions for a particular scenario. As attached in the pic below, this is my current situation. What i wanted to understand is,if i can get an output which looks like below All it does here is bin different ranks by insurers and then calculate the total premium under these ranks. I have taken InsD as an example. I am attaching my workbook over here. Please let me know on how to approach this interesting problem.

Best Regards,

Sachi

• ###### 1. Re: Calculating TotalSum per dynamically calculated Ranks
1 of 1 people found this helpful
• ###### 2. Re: Calculating TotalSum per dynamically calculated Ranks

Shin, thank you for the ping.

Your solution with Table Calcs is straightforward, though it doesn't scale well --

the maximum number of Client-Insurer unique values combinations should be known in advance.

There would be alternatives, for which the ranks could be

pre-calculated in the datasource -- call it the 'hard ranking'.

One way is to use the properties of the datasource underlying RDBMS,

which should be a SQL-2003 compliant, capable of executing Window Functions.

Connecting Live to such RDBMS, one could write the following RAWSQL Calculation:

// Rank by Prob of Winning

RAWSQL_INT(

"rank() over(partition by %2 order by %1 desc)"

, SUM( [Prob Of Winning] )

, MIN( [Client] )

)

then use this (aggregate) calculation in a FIXED LOD expression:

{FIXED [Client], [insurer] : [Rank by Prob of Winning] }

The latter is formally a Row-Level calculation,

so it's equivalent to a 'Materialized Rank' --

as if it's calculated right in the datasource.

Another approach -- call it an 'old-school SQL' one --

needs some data re-shaping (a range join in particular).

As I could recall, the approach has been introduced to the Forums

by Joe Mako, please refer to this thread for example:

How to show rank for filtered list while retaining rank of unfiltered list

Recently, Jonathan Drummey and Joshua Milligan

had been proposed the Ideas on row numbering & sorting in Prep,

which (if implemented) would allow to do ranking in the datasource:

https://community.tableau.com/ideas/8714

https://community.tableau.com/ideas/8715

https://community.tableau.com/ideas/8732

But even with version 10.2 (not to mention Prep ;-)

one could prepare a Self-Joined datasource

and do the 'hard ranking' via FIXED LOD expressions.

Please find the attached as an example of the latter.

Yours,

Yuri

3 of 3 people found this helpful
• ###### 3. Re: Calculating TotalSum per dynamically calculated Ranks

Sorry for the late reply, and thank you very much.

I had put the data re-shaping out of the scope, but definitely I should use that, and I used that approach here.

Thanks,

Shin