Multi Dimension Ranking
I have 2 modules, 1 has dimensions of TenantGroup, ReportPeriod and UserRole namely AgingperGroup. And the other module has TenantGroup and UserRole namely AgingGroupRanking. The latest module will take the data from the previous and lookup the CurrentPeriod parameter to get the exact data(of which data period) to be ranked for later.
Since I want each of the user who has UserRole to play around without effecting another Role's data, the dimension of the modules has to contain UserRole. But I have trouble to create a ranking system which should be have different result for each user.
the current result I got using RANK() is that from total 30 Tenant Groups, it multiplies with UserRole number and the last ranking become 120 instead of 30(per each user).
Is there a way to work around to get the right result of ranking?