CODEWITHSUNDEEP
amazon-quicksight
MaciekR
MaciekR

Reputation: 292

How to setup a control with month names

In my db i have a column created_at (date_time format). I would like to create an analytical dashboard when it will be possible to filter data by create_at by giving month name (one or more - multiple choice)

I have added a new calculated field "month" in data set with following formula:

formatDate(truncDate("MM",{created_at}),"MM" )

As a result i get new column with strings like "01", "02" etc.

I've created a new parameter: name: Month Data type: string Values: multiple values: {01, 02, 03 ....}

I've created a new control: name: Month Style: Multi select drop down Values: Link to a data set field -> Data set -> Month column

I've created a new filter for Month columns, based on Month parameter

My problem is: how to achieve the same result (filtering by month, multi select) having month names displayed in the control, not "01", "02" etc. Is it possible?

UPDATE

It is much better to have following formula in Month calculated field:

extract("MM",{create_at})

But it does not solve my problem....

Upvotes: 2

Views: 1600

Answers (1)

MaciekR
MaciekR

Reputation: 292

What i did, but i really don't like this solution as it is so ugly.....Any better solution is welcome :)

  • new calculated field month_number, with formula:

    extract("MM",{create_at})

  • new calculated field month_name with formula:

    ifelse({Month_number}=1,"Jan",ifelse({Month_number}=2,"Feb",ifelse({Month_number}=3, "Mar",ifelse({Month_number}=4, "Apr",ifelse({Month_number}=5, "May",ifelse({Month_number}=6, "Jun",ifelse({Month_number}=7, "Jul",ifelse({Month_number}=8, "Aug",ifelse({Month_number}=9, "Sep",ifelse({Month_number}=10, "Oct",ifelse({Month_number}=11, "Nov",ifelse({Month_number}=12, "Dec","Error"))))))))))))

Upvotes: 4

Related Questions