Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
NickHoff
Specialist
Specialist

Rank on multiple dynamic ad-hoc dimensions

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)

0 Replies