
1. Re: Calculating TotalSum per dynamically calculated Ranks
Shinichiro Murakami Jul 7, 2018 10:54 AM (in response to Sachidanand bommegowda)1 of 1 people found this helpfulHI 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

2. Re: Calculating TotalSum per dynamically calculated Ranks
Yuriy Fal Jul 7, 2018 2:11 PM (in response to Shinichiro Murakami)Shin, thank you for the ping.
Your solution with Table Calcs is straightforward, though it doesn't scale well 
the maximum number of ClientInsurer unique values combinations should be known in advance.
There would be alternatives, for which the ranks could be
precalculated in the datasource  call it the 'hard ranking'.
One way is to use the properties of the datasource underlying RDBMS,
which should be a SQL2003 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 RowLevel calculation,
so it's equivalent to a 'Materialized Rank' 
as if it's calculated right in the datasource.
Another approach  call it an 'oldschool SQL' one 
needs some data reshaping (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 SelfJoined datasource
and do the 'hard ranking' via FIXED LOD expressions.
Please find the attached as an example of the latter.
Yours,
Yuri

TestBook_v10.2_YF.twbx 80.9 KB


3. Re: Calculating TotalSum per dynamically calculated Ranks
Shinichiro Murakami Jul 10, 2018 11:34 AM (in response to Yuriy Fal)Hi Yuriy Fal
Sorry for the late reply, and thank you very much.
I had put the data reshaping out of the scope, but definitely I should use that, and I used that approach here.
Count number of students according to Rolling Average performance
Thanks,
Shin