Date syntex

Question & Answer
1

Hi there,

I am aggregating a metric call “mals,” for 2023 year-to-date, and I found an archived example that I can use. However, I’m getting a syntax error of “The syntax of the calculated field expression is incorrect. Correct the syntax and choose create again” with my query below:

ifelse(dateDiff({date_value},$now(),“YYYY” = 0 AND {date_value}<=$now(), {mals}, 0)

I am new to this but the query part makes sense to me. However I’m not sure what “$” does in this case and “$” isn’t in the QuickSight list of operators. Any help with the query is greatly appreciated.

2

You need to get rid of the $

ifelse(dateDiff({date_value},now(),“YYYY”) = 0 AND {date_value}<=now(), {mals}, 0)

The second issue is that you have your ‘mals’ calculation as an aggregation.

There are a couple work arounds to this.

  1. trying to bring your aggregation into your calculated field like this.
sum(ifelse(dateDiff({date_value},now(),“YYYY”) = 0 AND {date_value}<=now(), {original_mal}, 0))
  1. use aggregations on all of your other fields.
ifelse(dateDiff(firstValue({date_value}.[date_value ASC],[date_value]),now(),“YYYY”) = 0 AND firstValue({date_value},[{date_value} ASC],[{date_value}])<=now(), {mals}, avg(0))
3

Hi Max,

Thank you for your response. I tried removing the $ before posting, and maybe I am missing something. Even with your suggestions, I can’t get past the syntax error for some reason. Please see screenshot below.

image
image945×161 6.67 KB

image
image1036×203 9.67 KB

image
image1317×101 5.47 KB

4

  1. Do you have a field called {original_mal}? How are you calculating {mals}? I meant that you could do it here instead.

  2. You need to take out the period. Here’s an example using my fields. You need to substitute yours.

ifelse(dateDiff(firstValue({arrival_timestamp},[{arrival_timestamp} ASC],[{arrival_timestamp}]),now(),'YYYY') = 0 AND firstValue({arrival_timestamp},[{arrival_timestamp} ASC],[{arrival_timestamp}])<=now(), sum({arrival_timestamp_int}), avg(0))