Default sort order for aggregate table?

This is not totally clear and has tripped me up severla times. The Order by statement in the Models advanced tab does not accept Field Alias values. You have to put the full function + field value there. Here is an example:

I have a model aggregating dates by Year and Month.

Then the Order By Statement looks like this: CALENDAR_YEAR(Date__c) DESC, CALENDAR_MONTH(Date__c) DESC, DAY_IN_MONTH(Date__c) DESC

Pro Tip. In order to have a Year, Month and Day grouping on the same field - you need to jump over to the XML and add the second and third grouping row.

Worked perfectly. Thank you!

Can you group on a related field that is a date/time field? I have tried numerous combinations and the model will not sort.

Example:
MAX(Object1__r.Object2__r.Field_Name__c) DESC

Tami.  I was able to go up one level and sort on the parent’s create date using the syntax you show above. 

One hack is to add the field to your table,  make it oderable, preview the page and order by the field.  Then dig in the javascript to see the SOQL statement and look for the ORDER BY statement.   

(Hint: Instpect element on the page. Open the console.  Type  skuid.$M(‘ModelName’).soql  )

This is what I got: 
SELECT MAX(CreatedDate) maxCreatedDate,MAX(Opportunity.CreatedDate) maxOpportunityCreatedDate,Opportunity.Account.Name opportunityAccountName FROM OpportunityLineItem GROUP BY Opportunity.Account.Name ORDER BY MAX(Opportunity.CreatedDate) ASC NULLS LAST"

When I pulled out MAX(Opportunity.CreatedDate) and made it my default order - things were beautiful. 

Hi Rob,

Thanks for the detailed response! The SOQL hack proved to be very helpful. The ‘NULLS LAST’ was needed in order for the ordering to work, without that piece it wouldn’t work.

So the order by line looks like this:

MAX(Object_1__r.Object_2__r.Field_Name__c) DESC NULLS LAST

An update… my pro tip above is no longer necessary.  Now you can add date fields to the “grouping” section of an aggregate model - MULTIPLE TIMES!   (yes I’m yelling).  This means you can add a date field grouping for year,  and for month - using the same field. 

Yay… 

wow! Glad this is now available!