Table Subtotal Sorting

Options
BlueRooster
BlueRooster Domo Employee
in Charting & Analyzer

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

Tagged:

Best Answer

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • BlueRooster
    BlueRooster Domo Employee

    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
    ST_Superman Domo Employee

    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

     

  • AS
    AS Coach

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • BlueRooster
    BlueRooster Domo Employee

    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. 

This discussion has been closed.

Categories