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

HI Sachidanand,

There should be much more sophisticated way (Yuriy Fal , if you could help ?),

but anyways, the view shows expected results.

"Rank" cannot create dimensions and all the calc needs to consider the appropriate table calc setting.

There still multiple value in each box.

Thanks,

Shin

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

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