Date Range Filter - Show only Month-Year in the range

[Benjamin Beall 2]

I have a column called test_date which is in the MM/DD/YYYY format. I would like to use it as a date range filter within the text area of my dashboard. Is there a way I can show only Month and Year of the test date in my filter rangerather than showing it as MM/DD/YYYY

I am trying the below calculated column -

Concatenate (Month([test_date]), " ", Year([test_date]))

However, it is of the format "String" and not "Date". Additionally, my current test date is for 2 months (Sep and Oct) but after using the above calculated column, my test date range filter shows reversed dates Oct 2017 - Sep 2017 (it should show Sep 2017 - Oct 2017)

[Gaia Paolini]

Hi

I don't think you can use a date range filter for an incomplete date (i.e. without the day). I see what you mean about the concatenation of year and month becoming a string - and so it will be sorted alphabetically (Oct coming before Sep).

You could create a hierarchy (same way as a calculated column but choosing the hierarchy option) and add the year and the month of the date (in this order) to the hierarchy, then save it . Then create a filter with this hierarchy. The filter will appear like the snapshot below (I expanded the first year but initially it would be all collapsed).

Gaia

[Benjamin Beall 2]

Thank you Gaia once again for your help. The hierarchy option works fine. But a follow up silly question, when I add the hierarchy as a filter, I cant control the size of the hierarchy. The height becomes too huge to fit in my text area.

[Gaia Paolini]

Not a silly question. Indeed a filter in the text area appears not resizeable in height, and the hierarchy filter's default height is quite generous. However, it looks like it has a fixed size regardless of the data in the hierarchy, so it is not going to change with new data. Is it possible for you to resize or reposition the textarea to accommodate it 

 

Alternatively you could try using a TERR expression to control the month data type. So adding a calculated column using this formula:

 

TERR_String("output <- strftime(input1,'%Y-%m')",[test_date])

 

would give you a result similar to what you had initially, but with the month expressed as a number, year first. You can then add a filter based on this new column, which would be of more reasonable height.

 

Hope that helps

 

Gaia

[Benjamin Beall 2]

Thank you! that helps to solve my case.

[Haley Han]

Hi,

Actually, it can be done by change the date format of your date filter. Go to Filters -> Format Values -> Custom -> then input what date format you like.

Then you can create a range filter based on this date which only shows month and year.