Mega Table Row Grouping and Ordering

major_ace

Hello Gurus -

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

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.

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

MarkSnodgrass

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.

ArborRose

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.

MarkSnodgrass

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.

ArborRose

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.

major_ace

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.:

GrantSmith

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