Sorting a Stacked Bar with Set Analysis

[Anonymous]

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_ID	Keyword	Major	Term
1234	Test	Major	Side Effect
1234	Test	Minor	Side Effect
4567	Test 2	Major	Something Else
4567	Test 2	Minor	Something Else
4567	Test	Major	Side Effect
1111	Test 3	Major	Side Effect
2222	Test 3	Major	Side Effect
3333	Test 3	Major	Side Effect
4444	Test 3	Major	Side Effect
5555	Test	Major	Side Effect
5555	Test	Minor	Side 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

[Anonymous]

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]

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]

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

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

[Anonymous]

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]

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

[Anonymous · Accepted solution]

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.