Mega Table Row Grouping and Ordering

major_ace
major_ace Member
in Cards, Dashboards, Stories

Hello Gurus -

I'm trying to achieve something like this orgnization of data coming from Excel:

Screen Shot 2023-04-03 at 9.51.24 AM.png

But from the data set I get this where the Teams are filterable, but I can't see a way to consolidate all of January nor how to get column Anniversary month to sort chronologically.

domo-table2.png

Thannks for any ideas tio get me in the right direction.

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    In Analyzer, for each of the amount fields, did you select an aggregation type? If not, then choose Sum and it will sum it by team and month, which looks like what you are trying to do.

    image.png

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ArborRose
    ArborRose Coach
    Answer ✓

    As Mark says…you need an aggregate (COUNT, SUM) on the amount field. Place the month field and the amount field next to it. To order the months in chronological order, you can create a calculated field using Month({your date}). That will give you 1 for Jan, 2 for Feb, etc. Place that in the sort and the data will line up in order.

    If you need to sum up something that doesn't match the grouping…you can use CASE. Such as CASE WHEN {your criteria} THEN {amount} ELSE 0 END.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    In Analyzer, for each of the amount fields, did you select an aggregation type? If not, then choose Sum and it will sum it by team and month, which looks like what you are trying to do.

    image.png

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ArborRose
    ArborRose Coach
    Answer ✓

    As Mark says…you need an aggregate (COUNT, SUM) on the amount field. Place the month field and the amount field next to it. To order the months in chronological order, you can create a calculated field using Month({your date}). That will give you 1 for Jan, 2 for Feb, etc. Place that in the sort and the data will line up in order.

    If you need to sum up something that doesn't match the grouping…you can use CASE. Such as CASE WHEN {your criteria} THEN {amount} ELSE 0 END.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • major_ace
    major_ace Member

    Thanks guys. Your ideas sparked n idea for me to try.

    The Team Name is a result of a Beast Mode CASE WHEN CSM Name = x then Team Tara, ELSE Team Asad. So I wasnt sure how much I could use the output of that to manipulate in order to merge the rows.

    What I ended up doing is:

    CASE
    WHEN MONTHNAME(Anniversary Date) = 'January' THEN '1 - January'
    WHEN MONTHNAME(Anniversary Date) = 'February' THEN '2 - February'
    WHEN MONTHNAME(Anniversary Date) = 'March' THEN '3 - March'
    WHEN MONTHNAME(Anniversary Date) = 'April' THEN '4 - April'
    WHEN MONTHNAME(Anniversary Date) = 'May' THEN '5 - May'
    WHEN MONTHNAME(Anniversary Date) = 'June' THEN '6 - June'
    WHEN MONTHNAME(Anniversary Date) = 'July' THEN '7 - July'
    WHEN MONTHNAME(Anniversary Date) = 'August' THEN '8 - August'
    WHEN MONTHNAME(Anniversary Date) = 'September' THEN '9 - September'
    WHEN MONTHNAME(Anniversary Date) = 'October' THEN '10 - October'
    WHEN MONTHNAME(Anniversary Date) = 'November' THEN '11 - November'
    WHEN MONTHNAME(Anniversary Date) = 'December' THEN '12 - December'
    ELSE 0
    END

    That gave me this, which I can Quick Filter for a specific team or month etc.:

    domo-table3.png

  • GrantSmith
    GrantSmith Coach
    edited April 2023

    One way to simplify your beast mode case statement is to instead utilize the FORMAT_DATE function:

    DATE_FORMAT(dt, '%m - %M')

    This will instead format your numbers as 2-character strings so they sort properly in your list and makes it a bit easier to read.

    04 - April

    Here's al ink to some documentation about the format strings you can use in the DATE_FORMAT function:

    https://www.w3schools.com/sql/func_mysql_date_format.asp

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories