1 Reply Latest reply on Apr 28, 2016 4:11 PM by diego.medrano

    multi-value sorting (using parameters, context, Top N, and nested LOD)

    Zach Leber

      Comments, questions, and suggestions welcome!


      My actual workbook is sorting cancer patients by DNA mutation locations to look for correlations across genes. But this mock-up has all the same challenges. It uses a context filter to select a state, then two Top N filters to select the top customers and sub-categories for the state, then adds colored marks to show whether the customer has ordered from that sub-category, with the color encoding ship mode.

      multi-value sort.png

      The first challenge is sorting a dimension in order to move certain categories of particular user interest to the top of the list. We can parse a comma-separated parameter string to pull out multiple elements. Here we allow the user to enter one or more sub-categories and have them move to the top of the list based on the order they specify them.


      SubCat sort value

      // use our parameter to move certain categories to the top

      // ZN guards against 1/0, converts to 0 which is what we want

      // 1/FIND returns larger values for entries at the beginning of the list

      ZN(1/(FIND([SubCat List (CSV)], [Sub-Category])))


      If we enter "Tables, Chairs" into the parameter, those two categories "bubble" to the top, the rest remain as is.

      multi-value sort 2.png

      The second challenge is sorting the customers from left to right to see the customers that buy from our categories of interest. Here we use a nested LOD expression to assign a weighted value to each customer depending on whether they have bought from the category. This clustering allows us to look for correlations (e.g. do people usually buy chairs when they buy tables?). See the above image for how we've now clustered the dimensions into the top left.


      Customer sort value

      // sort customers from left to right depending on if they have bought from the list

      // customers with purchases from items earlier in the list are prioritized

      { FIXED [Customer Name] : SUM({ INCLUDE [Sub-Category]: MAX([SubCat sort value]) }) }


      // downside of using FIXED is that it runs before our Top N filters so could

      // include unlisted sub-categories that happen to match our list

      // to guard against that, use the following instead (add any additional view dimensions):

      // { EXCLUDE [Sub-Category], [Ship Mode] : SUM({ INCLUDE [Sub-Category]: MAX([SubCat sort value]) }) }


      This reminds us of the order of operations, i.e. FIXED runs after context filters but before view filters (our Top Ns) so could include data not displayed. If we weren't already putting state in context, we could put the Top Ns in context, and we wouldn't have this problem. But we can't do that. In my real workbook I have to use the EXCLUDE version (which requires maintenance as the view is modified) instead of the simpler FIXED version.


      It all works (I think). Again, comments, questions, and suggestions welcome! I've attached a 9.3 packaged workbook.