Can one create a date constant in a QuickSight calculation

Question & Answer
,
1

I have a parameter that has two fixed values ‘YTD’ and ‘FYTD’ FYTD stands for Fiscal Year to Date. Our fiscal year starts on Dec 1 of the preceding year and ends on Nov 30.

I’d like to create a calculation based on this parameter (named pStartDt) that looks like this

ifelse(${pStartDt} = 'FYTD', date '2021-12-01', date '2022-01-01')

I want this field to have a date type.

No matter what I do, it comes back with syntax errors. In SQL I would just do a to_date() function.

Thanks

2

Hi,

Can you test with parsedate (parseDate - Amazon QuickSight ) ?
My data source : RDS PostgreSQL

ifelse(${pStartDt}=‘FYTD’,parseDate(‘2012-12-01’),parseDate(‘2022-01-01’))

Screenshot 2022-08-19 at 09.51.20
Screenshot 2022-08-19 at 09.51.20857×444 33.5 KB

1 Like
3

Try creating a calcfield in your dataset that converts date into fiscal_data. Something like this:

ifelse(
    extract('MM',{Timestamp}) >= 12,
        addDateTime(1,'YYYY',{Timestamp}),
    {Timestamp}
        )

Then create another calcfield in the analysis using your parameter that determines which value to display. Something like this:

ifelse(
    ${CalendarType}="YTD",
        Timestamp,
    ${CalendarType}="FYTD",
        {f_date},
    NULL)

Use this 2nd calcfield in your field wells, and make sure that you set it to sort by the original timestamp field. This way the correct fiscal year will show, but the sort order will remain chronological.

4

Koushik,
Thanks for your reply. It does what I want it to do. I too had looked at the parsedate function but had been confused by this sentence “This function returns all rows that contain a date in a valid format and skips any rows that don’t, including rows that contain null values.” My brain, strangely enough, grossly misinterpreted this statement – I don’t want to describe how bad it was.

Lulemena
I haven’t had a chance to review your reply–I can tell it’s a completely different approach.