Converting global_time to date for lookup file

[vm_molson]

How do I take a dashboard global time (i.e. - $global_time.earliest$, $global_time.latest$) and convert it into a date to be used when searching a lookup file that only has a date column (i.e. - 04/15/2024)?

[KendallW]

Hi @vm_molson is the search for the lookup file within the same dashboard, or some other dashboard linked from a drilldown? 

If it's within the same dashboard, you can simply add something like this to the search for the lookup:

| search date<=$global_time.latest$ date>=$global_time.earliest$

However if you want to link to a different search, you might need to go down this route (link) where you would add the variables directly in the URL parameters of the link the user would click on. 

[vm_molson]

Thanks for the quick reply! One correction to something I said earlier: the format of the "Date" in my lookup file is YYYY-MM-DD.

It is in the same dashboard. I tried what you had mentioned already, but with the global parameters within quotes. That didn't seem to return what I wanted, but it did not lead to an error. Then I tried without quotes, and I get this error:

Error in 'where' command: The operator at 'mon@mon AND Date<=@mon ' is invalid.

The where clause is like:

where customer="XYZ" AND Date>=$global_time.earliest$" AND Date<=$global_time.latest$"

I've also tried this:

| inputlookup mylookup.csv
| eval lookupfiledatestart =strftime($global_time.earliest$, "%Y-%m-%d")
| eval lookupfiledateend =strftime($global_time.latest$, "%Y-%m-%d")
| where client="XYZ" AND Date>=lookupfiledatestart AND Date<=lookupfiledateend

That gives me this error:

Error in 'EvalCommand': The expression is malformed. An unexpected character is reached at '@mon, "%Y-%m-%d")'.