0

I made a parameter with a custom list of options 'MM', 'YYYY', and 'Q'. When a user selects one, I planned my calculated field to use it as an argument for the extract() function, like this:

extract(${period}, date)

I tried to omit the quotes, include them, but nothing works, saying "At least one of the arguments in this function does not have correct type."

Is what I want to make possible?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Big Chungus
  • 185
  • 1
  • 3
  • 13

1 Answers1

0

From the little testing I've done it looks like extract requires a string literal as its first argument. This could be a bug and may be worth bringing to Amazon's attention.

As a workaround, you could solve this by using ifelse

ifelse(
  ${period} = 'MM', extract('MM', {Date}),
  ${period} = 'YYYY', extract('YYYY', {Date}),
  extract('Q', {Date})
)

This is actually kind of nice because it gives you the opportunity to make the filter control more readable (e.g. Month, Year, Quarter) then do

ifelse(
  ${period} = 'Month', extract('MM', {Date}),
  ${period} = 'Year', extract('YYYY', {Date}),
  extract('Q', {Date})
)

This works for your example because your grouping options are well defined, however, it wouldn't work for a dynamic, less understood set of controls.

mjgpy3
  • 8,597
  • 5
  • 30
  • 51