Why would you want a symbol that sorts after “Z” in ascending order?  Well, here is an alternate title:  How To Place the Summary or Total Row Last or at the Bottom of the Table.

I’ve run into the scenario multiple times where I am creating a total or subtotal row that summarizes the other rows in the table.  This is also something I’ve done if I have a bar chart and want one of the bars to summarize the rest.  If I leave it alone or force a “-” symbol before and after, like “- Total -” it actually places this at the very top of the table.

q1 = load "DTC_Opportunity_SAMPLE";

q1 = group q1 by 'Opportunity_Owner';
q1 = foreach q1 generate 'Opportunity_Owner' as 'Opportunity_Owner', count() as 'Count';
q1 = order q1 by ('Opportunity_Owner' asc);

z1 = group q1 by 'Opportunity_Owner';
z1 = foreach z1 generate "- Total -" as 'Opportunity_Owner', sum(sum('Count')) over([..] partition by all) as 'Count';
z1 = limit z1 1;

q1 = limit q1 2000;
q1 = union z1, q1;

q1 = group q1 by 'Opportunity_Owner';
q1 = foreach q1 generate 'Opportunity_Owner' as 'Opportunity_Owner', sum('Count') as 'Count';
q1 = order q1 by 'Opportunity_Owner' asc;

The end result is a table with the summary total at the top:

Sometimes we want the Summary or Total row to be at the bottom, or the summary bar (in the case of a bar chart) to be last.  We want the summary row last while still keeping the remaining rows sorted in ascending or alphabetical order.

How To Place Summary or Total Row Last / At the Bottom

Now it would be easy to place the total row at the bottom if we just change the last line of code to ‘desc’ instead of ‘asc.’  The problem with this approach is that it would sort all of the names in descending order and they would be in reverse alphabetical.

Another approach is to reorder the union so that z1 is listed second instead of first.  However, if you need to re-project after the union as you often need to do this does not help.

The solution here is to place a symbol that sorts after z as the text of the ‘Total’ row and leave it sorted in ascending order.  According to a post on stackoverflow, here are some symbols that sort after z:

• Ω (option+z)
• π (option+p)
• µ (option+m)

Reference: Symbols that sort after z

If we place one of these symbols at the beginning of the name instead of “- Total -” it will ensure that the row always sorts last.  Even better, you can place it into a new “helper column” named ‘Sort’ and use it to sort and then discard it at the very end:

q1 = load "DTC_Opportunity_SAMPLE";

q1 = group q1 by 'Opportunity_Owner';
q1 = foreach q1 generate 'Opportunity_Owner' as 'Opportunity_Owner', 'Opportunity_Owner' as 'Sort', count() as 'Count';
q1 = order q1 by ('Opportunity_Owner' asc);

z1 = group q1 by 'Opportunity_Owner';
z1 = foreach z1 generate "- Total -" as 'Opportunity_Owner', "ΩTotal" as 'Sort', sum(sum('Count')) over([..] partition by all) as 'Count';
z1 = limit z1 1;

q1 = limit q1 2000;
q1 = union z1, q1;

q1 = group q1 by ('Opportunity_Owner','Sort');

q1 = foreach q1 generate 'Opportunity_Owner' as 'Opportunity_Owner', 'Sort' as 'Sort', sum('Count') as 'Count';
q1 = order q1 by 'Sort' asc;
q1 = foreach q1 generate 'Opportunity_Owner' as 'Opportunity_Owner', sum('Count') as 'Count';

This results in a table as we would expect:

To summarize, we are using one of the symbols that sorts after “z” in the name of a new column and then sorting by it to ensure that this row is always last.  Then, we discard that column so you never know it was there!

Let me know your thoughts, comments, suggestions or other workarounds you’ve found to present the data to meet the needs of your users!