Rank on multiple dynamic ad-hoc dimensions

NickHoff — Thu, 27 Sep 2018 20:28:50 GMT

Hello,

I have an inline table that is defined as the following for my first selection.

RankOrganizationalDimensions:

LOAD * INLINE [

@RankOrgNum, @RankOrgDesc, @RankOrgDisplay

01, Company, 'Company'

02, Group, GroupName

03, Division, DivisionName

04, Market, MarketName

05, Facility, FacilityName

];

My second selection is dynamic:

ESLDimensions:

LOAD * INLINE [

@PLRankNum, @PLRankDesc

01, Level 1, $(vPLLevel1)

02, Inpatient Med/Surg, IPMedSurg

03, Level 2, $(vPLLevel2)

04, Level 3, $(vPLLevel3)

05, Level 4, $(vPLLevel4)

06, Level 5, $(vPLLevel5)

];

With the variable defined as:

SET vPLLevel1 = "=@PL_Level1";

SET vPLLevel2 = "=@PL_Level2";

SET vPLLevel3 = "=@PL_Level3";

SET vPLLevel4 = "=@PL_Level4";

SET vPLLevel5 = "=@PL_Level5";

I have a straight table with the following condition

GetSelectedCount(@RankOrgDesc) > 0

AND

GetSelectedCount(@PLRankDesc) > 0

AND

(GetSelectedCount(@VarFixAdHocMetric) > 0 OR GetSelectedCount(@DirIndAdHocMetric) > 0)

With each dimension in the table with a condition as =SubStringCount(Concat(@RankOrgNum,'|'),'02') - for the example of Group.

This creates an ad-hoc like report, but there are three levels of selections and I want to add a rank option to a single expression at a time. =SubStringCount(Concat(@AdHocMetricNum,'|'),'001') would be the example of getting Volume.

However, when I add a rank with the same @RankOrgNum conditional and ranking on the metric name i.e. Rank([Volume]) it will only rank at the first level. How can I get this to rank the @RankOrgDesc level by @PLRankDesc . The number of dimensions selected with typically be 2, but it could and will be more than 2 a times.

The closest I came is adding each metric's expression in a field @PLRankCalc then calling rank like this:

Where vRank = =@PLRankCalc

=MaxString({<@RankOrgDesc = {'&CHR(34)&'=RANK('&'$'&'(vRank),4)=1'&CHR(34)&'}>}@RankOrgDesc)

topic Rank on multiple dynamic ad-hoc dimensions in QlikView

Rank on multiple dynamic ad-hoc dimensions