15 Replies Latest reply: Aug 25, 2017 4:08 PM by Erik Wilkinson RSS

    Sorting a Stacked Bar with Set Analysis

    Erik Wilkinson

      All - I have what I hope is an easy question.  Though I've hit a wall.  I have a rather simple set of data:

          

      Document_IDKeywordMajorTerm
      1234TestMajorSide Effect
      1234TestMinorSide Effect
      4567Test 2MajorSomething Else
      4567Test 2MinorSomething Else
      4567TestMajorSide Effect
      1111Test 3MajorSide Effect
      2222Test 3MajorSide Effect
      3333Test 3MajorSide Effect
      4444Test 3MajorSide Effect
      5555TestMajorSide Effect
      5555TestMinorSide Effect

      One document could have many keywords that are either major or minor (or have the same keyword marked both ways).  The keywords are grouped into Terms.

       

      I'm trying to create a sheet that shows a stacked bar counting the keywords by distinct document ID for only the Term "Side Effect".

       

      If I do this as a regular bar chart with Keyword as a dimension and a measure of:

       

      Count({$<Term={"Side Effect"}>} DISTINCT Document_ID)

       

      This works great.  I'm sorting by keyword in descending order.  "Sort by expression" using the same formula as the measure.

       

      As soon as I add another dimension (Major) and stack it I get unexpected results:

       

      I'm using the same measure and sort formula.  I would have thought that "Test 2" would be excluded since there is none with a Term of "Side Effect".  The sort is also off as it's counting the distinct Document ID by Major AND Keyword now and appending them together.

       

      I'm guessing this is a misunderstanding of set analysis on my part.  Any advice or direction appreciated.

       

      Source data and QVF attached.

       

      Thanks,

       

      Erik