Sorting specific value to top

[#1]

Hi all,

I have a straight table in which I'm trying to sort by a couple of dimensions but I'm having trouble with the usual methods.

Fields are as follows:

ProductID

Product Default DepartmentID

Department ID

TotalSales

So to explain the above, each product has a default DepartmentID, but can have activity from other departments in the 'TotalSales' field. The straight table looks a bit like this:

ProductID	Product DefaultDeptID	DepartmentID	TotalSales
AA1	ABC5	ABC1	12
AA1	ABC5	ABC2	55
AA1	ABC5	ABC4	18
AA1	ABC5	ABC5	19
BB2	ABC3	ABC4	55
BB2	ABC3	ABC7	74
BB2	ABC3	ABC3	14
CC3	ABC2	ABC2	30
CC3	ABC2	ABC5	97
CC3	ABC2	ABC3	18

The sort order I need in this table should be:

ProductID (Text; Ascending)

Then I need to sort the DepartmentID value, putting the DepartmentID which matches the ProductDefaultDeptID to the top of the group(bold in the above table) and then the sort order of the remaining DepartmentID/Value is not important.

So of course, ProductID is promoted to the top of the list in the Sort tab and sorted by text(ascending); DepartmentID is second in the list. I've tried expressions such as

if([DepartmentID]=[ProductDefaultDeptID],1,2)

if([DepartmentID]=[ProductDefaultDeptID],1,0)

=[DepartmentID]=[ProductDefaultDeptID]

=WildMatch([DepartmentID],[ProductDefaultDeptID],'?')

=WildMatch([DepartmentID],[ProductDefaultDeptID],'*')

=MixMatch([DepartmentID],[ProductDefaultDeptID],'*')

The above expressions I have tested out as a calculated dimension in the same table to check they are evaluating as expected which it always is.

It seems like a fairly simple requirement; and the attempted solutions so far seem very straightforward and very similar to things I've done in the past for custom sort orders. If anyone has any insight into this problem I'd appreciate their input - I'm hoping this has a solution and is not a bug with QV.

I'm using v11.20 SR13

Thanks

[Gysbert_Wassenaar · Accepted solution]

Add another expression match([Product Default DepartmentID],Department ID). Sort on that expression Numeric Value - descending. Then hide the expression on the Presentation tab.

[#3]

Thanks Gysbert, that has worked perfectly!

That's a great solution!