Month/Year dropdown filter

Q&A
, , , ,
1

Hi

I’m trying to use a date filter for a date field to display similar to the below that can be done in Tableau. Currently i can only see the option for a relative date or a manual date range picking start and end dates but i would like it to be like the below ?

image
image118×521 4.82 KB

2

Hi @steven.fisher

Welcome to the Quick Suite community!

Use the extract function to derive the year and month number from your date field, then use an ifelse based on the month number to generate the month name. Finally, concatenate the month name and year to create a Month/Year field that you can use as a filter.

Year -  extract('YYYY', {Date Field})

MonthNo  -  extract('MM', {Date Field})

MonthName -
ifelse(
    {MonthNo}  = 1, 'Jan',
    {MonthNo}  = 2, 'Feb',
    {MonthNo}  = 3, 'March',
    {MonthNo}  = 4, 'April',
    {MonthNo}  = 5, 'May',
    {MonthNo}  = 6, 'June',
    {MonthNo}  = 7, 'July',
    {MonthNo}  = 8, 'Aug',
    {MonthNo}  = 9, 'Sept',
    {MonthNo}  = 10, 'Oct',
    {MonthNo}  = 11, 'Nov',
    {MonthNo}  = 12, 'Dec',
    NULL
)

Month/Year - concat(MonthName , ' ', Year )
3

Thank you for the reply but i am getting a message saying error found with a red line under the M of the MonthNo, with no further details

4

Hi @steven.fisher

Could please share the error message and make sure to replace the date field from your dataset.

5

HI @Xclipse

this is the error when i hover over the red line

image

6

Hi @steven.fisher

Please remove Year - and use only extract('YYYY', {Date Field}).

The following are the calculated field names

  • Year
  • MonthNo
  • MonthName
  • Month/Year
7

Thanks @Xclipse

It’s getting closer but now the error has moved to the last line

image

8

Hi @steven.fisher

Please remove MonthNo - and use only extract('MM', {Date Field}) and for “MonthName” calculation remove MonthName - and use only
ifelse( {MonthNo} = 1, 'Jan', {MonthNo} = 2, 'Feb', {MonthNo} = 3, 'March', {MonthNo} = 4, 'April', {MonthNo} = 5, 'May', {MonthNo} = 6, 'June', {MonthNo} = 7, 'July', {MonthNo} = 8, 'Aug', {MonthNo} = 9, 'Sept', {MonthNo} = 10, 'Oct', {MonthNo} = 11, 'Nov', {MonthNo} = 12, 'Dec', NULL ) and for “Month/Year” calculation remove Month/Year - and use only concat(MonthName , ' ', Year )

9

HI @Xclipse

the error has moved again

image

extract(‘YYYY’, {Reporting Month})

extract(‘MM’, {Reporting Month})

ifelse(

{MonthNo}  = 1, 'Jan',

{MonthNo}  = 2, 'Feb',

{MonthNo}  = 3, 'March',

{MonthNo}  = 4, 'April',

{MonthNo}  = 5, 'May',

{MonthNo}  = 6, 'June',

{MonthNo}  = 7, 'July',

{MonthNo}  = 8, 'Aug',

{MonthNo}  = 9, 'Sept',

{MonthNo}  = 10, 'Oct',

{MonthNo}  = 11, 'Nov',

{MonthNo}  = 12, 'Dec',

NULL

)

concat(MonthName , ’ ', Year )

10

Hi @steven.fisher

You should create 4 different calculated fields

Example:
Note: Replace the date field from your dataset

  • Year - Extract the year from the Date field
extract('YYYY', {Date Field})
  • MonthNo - Extract the month number from the Date field
extract('MM', {Date Field})
  • MonthName - Get the month name based on the MonthNo calculated field
ifelse(
    {MonthNo}  = 1, 'Jan',
    {MonthNo}  = 2, 'Feb',
    {MonthNo}  = 3, 'March',
    {MonthNo}  = 4, 'April',
    {MonthNo}  = 5, 'May',
    {MonthNo}  = 6, 'June',
    {MonthNo}  = 7, 'July',
    {MonthNo}  = 8, 'Aug',
    {MonthNo}  = 9, 'Sept',
    {MonthNo}  = 10, 'Oct',
    {MonthNo}  = 11, 'Nov',
    {MonthNo}  = 12, 'Dec',
    NULL
)
  • Month/Year - Concatenate the MonthName and Year calculated fields
concat(MonthName , ' ', toString(Year))

Year

image
image462×168 3.38 KB

MonthNo

image
image367×176 3.45 KB

MonthName

image
image311×522 8.95 KB

Month/Year

image
image477×195 4.44 KB

11

Thanks that works but if i use it as a filter its in alphabetical order as this is a string do you know anyway i can get the months in the correct order of month/year rather than April 2023, April 2024, April 2025, Aug 2023 etc

12

Hi @steven.fisher

By default, the filter sorts the values alphabetically. If you want to sort the months in chronological order, you need to append the month number to the month name (for example, “01-Jan”) so that the sorting works numerically.

13

Thanks @Xclipse combining your suggestions with some trial and error i’ve resolved it