Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sorting a Stacked Bar with Set Analysis

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

15 Replies
Anonymous
Not applicable
Author

The use of TOTAL for sorting I'm thinking sorts everything by 7 - hence why things aren't changed when Descending or Ascending is toggled.  In that case it must sort alphabetically?

Save

Save

Anonymous
Not applicable
Author

I did some testing and it actually appears to be sorting by load order when using

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

as the formula for "sort by expression".

All I did was reorder the source data in Excel to put the "Test 3" keywords first.  QVF and source attached.  Unfortunately using TOTAL doesn't seem to work here.

Any other thoughts?  I did successfully create a sort formula that sort of works when you use it in sort by expression on keyword:

Count({$<Term={"Side Effect"}, Major={"Major"}>} DISTINCT Document_ID) + Count({$<Term={"Side Effect"}, Major={"Minor"}>} DISTINCT Document_ID)

That correctly adds the minor/major term counts together for sorting.  As long as I don't filter on Major.  Then it falls apart (and the users will want to filter on Major).

antoniotiman
Master III
Master III

If You want Ascending or Descending, then use this in formula of 'sort by expression'

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

Anonymous
Not applicable
Author

Thanks Antonio - I wish that was it!  But in your examples notice that they are not "ascending" and "descending".  In you first screenshot, if the order is "ascending" (I think that is "crescente" right?) then Test 3 with a value of 4 would be first followed by Test.

I appreciate you guys hanging with me.  This seems like it should be so deceptively simple....

antoniotiman
Master III
Master III

It is referred to Blu Bar Value only and not Blu+Red

Anonymous
Not applicable
Author

Well I think I solved my question by sorting on expression by:

sum(aggr(Count({$<Term={'Side Effect'}>} DISTINCT Document_ID),Major,Keyword))

I'm trying to find holes in this.