0

I have some input that contains a set of dates. I want to strip the day from the input and sort by just the month and year. I know there are other ways to do this, but why can't I set the format to yyyy-mm and then specify options no_values to ensure that the output does not include the day, at all (documentation reference)?

Example data is below. Notably, the output is sorted by day, which I do not want. I only want the output to be sorted by month. Note that this example contains only one month for simplicity.

input:

Date Val1
8/5/2015 10
8/27/2015 13
8/14/2015 4

formula: =query(A2:B,"select A,B where A is not null order by A asc format A 'yyyy-mm' options no_values",0)

output (when looking at cells):

Col1 Col2
2015-08 10
2015-08 4
2015-08 13

output (when looking at formula bar):

Col1 Col2
2015-08-05 10
2015-08-14 4
2015-08-27 13
4
  • It looks that "the problem" is that you are expecting that the sort is done after the options no_values but for we can see that is not the way that QUERY works. – Rubén Apr 20 at 20:51
  • @Rubén The output contains the day as the value (you cannot see it in my original post as only the month is shown, but if you view the cells you'll see that the day is present), so the options no_values seems to not be honored at all. Is this expected? – Chris Morris Apr 21 at 3:47
  • Please add a demo spreadsheet. or add two views of your data, one showing the displayed values and other showing the values displayed in the formula bar for each cell. – Rubén Apr 21 at 3:58
  • @Rubén done. thanks. – Chris Morris Apr 21 at 4:49
0

Try this:

=arrayformula( 
  query( 
    { text(A2:A, "yyyy-mm"), B2:B },
    "select Col1, Col2 
     where Col1 is not null 
     order by Col1 asc", 
    0 
  ) 
)
5
  • 1
    That would likely work (haven't checked yet), but I want to understand why my method is not working. Am I misreading the documentation? – Chris Morris Apr 20 at 20:44
  • From the documentation reference: "Note that data sources are not required to implement the query language, or if they do, to implement all features of the language. Unless you have reason to believe otherwise, you should not depend on a data source to implement all features of this language." – doubleunary Apr 21 at 5:50
  • So what does that mean? Google Sheets defines a spec for the query language and does not fully implement it, and does not document where it chooses not to? If true, that seems bizarre. – Chris Morris Apr 21 at 17:45
  • The query language is not specific to Google Sheets. It is associated with the Google Visualization API. – doubleunary Apr 21 at 17:55
  • Yes, you are correct. Sorry, I actually realized that even while making my previous comment. So it's not nearly as bizarre, though I still wish Sheets documented this limitation. Thanks! – Chris Morris Apr 22 at 9:47
0

It appears that Google Sheets does not support the options clause. This is acceptable, based on the documentation reference:

Note that data sources are not required to implement the query language, or if they do, to implement all features of the language. Unless you have reason to believe otherwise, you should not depend on a data source to implement all features of this language.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.