Limiting data using expression - with a date
A quick tip on using a date to limit data in a visualization with a date. I found this non-intuitive and undocumented.
This syntax for the boolean expression using a date works:
[SnapshotDt]=Date('5/24/2013')
Interestingly, Date() is not a documented function in the expression builder.
You may also wish to create a document property for your date rather than hard coding a single date.
I created a document property called ViewDate.
To filter data in my visualization to the value of this property, I used the boolean expression
[SnapshotDt]=Date('${ViewDate}')
[SnapshotDt]=${ViewDate} does not work!
PS - I am on version 4.0.
Attachments
| Attachment | Size |
|---|---|
 limitDataUsingExpression.png | 39.49 KB |
(5) Answers
Good afternoon Britt,
Great tip! The Date function does appear in the list of Functions -- just type 'Date' and you will see it's definition and examples:
Creates a Date from a column or values. If a single argument is given, the input column can be of type String or DateTime. If a String is specified, the date must be written on a form that Spotfire can recognize and all parts of the date (year, month and day) must be present.
If three integer arguments are given, then the first argument is the year, the second is the month and the third is the date in that month.
Example:
Date([Column])
Date(2007, 05, 17)
This function is also available in Spofire version 3.3.1
- Ray
Good day Kashi,
It seems as if you're trying to use the Spotfire DATE expression within an SQL statement; take a look at how the Date function is used in SQL http://technet.microsoft.com/en-us/library/ms186724.aspx.
Hope this helps,
Ray
Hi Ray,
Seek your advise on the following...
I want to filter out data from an infolink field, the source column of which of date type.
This one works:
...and spews out the error...
InformationModelException at Spotfire.Dxp.Data: Failed to execute query: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "Date(2011,09,30))" (10713) (HRESULT: 80131500)
Am I going wrong somewhere?
Regards,
Kashi
You are welcome,
I don't think I've ever used the Category drop-down -- I just leave it on 'All functions' and hope to find what I need in there
Suggestion Box: I think you submit a suggestion/ticket to the Support staff https://www.tibcommunity.com/groups/support
Good luck,
Ray
Ray -
Thanks for the reply (and correction!). That is interesting.
Perhaps a function can only be assigned to a single function group (besides ‘All Functions’)?
Date() is listed in ‘Conversion Functions’ but not in ‘Date Functions’.
Including Date() in both categories would be a nice-to-have to improve findability.
Where is the suggestion box?