Table Subtotal Sorting

On the table card, once you've enabled "Show Subtotal Rows", has anyone else encountered issues with it forcing the first row to be the primary field?

I've got a few cards where the first row is a formatted date (or some other non-numeric value) that looks like "January 2018" for several months. I love the ability to be able to have subtotals so I can aggregate other dimensions in the table. However, with it forcing the first column to be the primary sort field, the only way i've found to try to get it to display in the correct order is something like this:

CONCAT(
  CASE 
		WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN 1
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) THEN 2
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) THEN 3
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) THEN 4
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) THEN 5
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) THEN 6
		WHEN MONTH(`Date`) = MONTH(DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) AND YEAR(`Date`) = YEAR(DATE_SUB(CURDATE(), INTERVAL 6 MONTH)) THEN 7
  END,
  '. ', MONTHNAME(`Date`), ' ', YEAR(`Date`))

Which this appends a number based on how far back the month is. So I end up with "1. January 2018" or "2. December 2017".

Has anyone else found a better solution to this issue? Or maybe there's an option to disable subtotal sort that I'm just blanking on.

Sincerely,

ValiantSpur

Find more posts tagged with

Table

Accepted answers

Subtotal rows do automatically sort the first column in the primary position. The data would be hard to read otherwise (jumbled category elements but aggregated, misordered categories?). You'll see a faded column name in the sorting properties frame.

You can choose if this is done ascending or descending, though, which will behave differently based on the datatype. If you have a string, then it will be alphabetical and you'll need to add something to that string to force the sorting how you want it. So, you're on the right path within the confines of what we're allowed to do.

All comments

BlueRooster

Ok, after hearing it put that way it makes more sense. Without the sort on the first column it would indeed get very jumbled.

Thanks for the clarification.

ST_Superman

Try using this beastmode as the first column:

DATE_FORMAT(`Date of Invoice`,'%Y - %m')

This will give you a date format like 2017 - 12

You could also use something like this:

DATE_FORMAT(`Date of Invoice`,'%Y - %m %M')

This would give you 2017 - 12 December

You can't just go with %Y %M because it will sort the months alphabetically

Yes, sorting depends on what you want in your string @Valiant. I've done in the past on other card axes as @ST_-Superman-_ suggests, but it applies equally as well for table cards.

BlueRooster

Thanks @ST_-Superman-_

I'll keep that in mind for our date groupings, that's a good deal cleaner than what I was using.

I'll still probably have to use some long Case When's for some other cards that group by things like line items on a P&L, but at least it's workable.