AnsweredAssumed Answered

SQL: How to ORDER BY calc: number type

Question asked by openspace on Feb 28, 2015
Latest reply on Mar 2, 2015 by openspace

Good afternoon!

 

I have an SQL calculation that I am trying to deploy within the y-axis of a bar chart and I am encountering the not so desired result of a "?." If someone could help me troubleshoot this I would be jumping out of my seat with glee.

 

Here is the troublesome function:

ExecuteSQL(

"SELECT SUM(Total)

FROM Donations

WHERE DateCreation >= StartDate AND DateCreation <= EndDate

GROUP BY Month

ORDER BY Month DESC";

""; ¶)

 

Here are the fields:

DateCreation: unstored calculation, date type

StartDate: unstored calculation, date type

EndDate: unstored calculation, date type

Month: unstored, calculation, number type: Month(DateCreation)

 

After some quick experimentation I realized that if I change the Month field to a text type field and rejig the calculation from Month(DateCreation) to MonthName(DateCreation), the sql function results with the desired solution but it orders the months alphabetically rather than by the number of the month. Is there a way to order the sql function by a calculation field that is a number type rather than a text? Essentially I want the months to show in the order (1, 2, 3, 4, 5, 6, 7, 8 9, 10, 11, 12) on my chart. I am not familiar with SQL, so I apologize if my question is fairly simple.

Outcomes